I need to nest 2 arrays so I can echo the order header as well as the order details
I have updated my original post based on what I learned from your comments below. This is a much easier process than I originally thought.
require '../database.php';
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT * FROM Orders WHERE id = 430";
$q = $pdo->prepare($sql);
$q->execute(array($id));
$data = $q->fetch(PDO::FETCH_ASSOC);
echo 'Order Num: ' . $data['id'] . '<br>';
$sql = "SELECT * FROM Order_items
JOIN Parts ON Parts.id = Order_Items.part_id
WHERE Order_Items.orders_id = 430";
$q = $pdo->prepare($sql);
$q->execute(array($line_item_id));
$data = $q->fetch(PDO::FETCH_ASSOC);
while ($data = $q->fetch(PDO::FETCH_ASSOC))
{
echo '- ' . $data['part_num'] . $data['qty'] . "<br>";
}
Database::disconnect();
Unfortunately, only my first query gives results. The second query produces the following ERROR LOG: "Base table or view not found: 1146 Table" Order_items "does not exist", but I am expecting the following results.
Expected results for query 1: Order number: 430
Expected query results 2:
- Screws 400
- Plates 35
- Clips 37
- Poles 7
- Zip ties 45
source to share
Now that I understand where you are from, please explain a couple of things.
1.PDO and mysqli - two ways to access the database; they essentially do the same thing, but the notation is different.
2. Arrays are variables with several "compartments". The most typical array has bays identified by a numeric index, for example:
$array[0] = 'OR12345'; //order number
$array[1] = '2017-03-15'; //order date
$array[2] = 23; //id of a person/customer placing the order
etc .. But this will require us to remember which index of the number means what. Thus, there are associative arrays in PHP that allow you to use text strings as indices and are used to get the results of an SQL query.
3. Approval
$data = $q->fetch(PDO::FETCH_ASSOC)
or
$row = $result->fetch_assoc()
do the same: put the record (string) from the query into an array using the field names as indices. This way, it's easy to use the data because you can use the field names (a bit around them) to display or manipulate the field values.
4.The
while ($row = $result->fetch_assoc())
does two things. It checks ifthe string obtained from the query results. and although there is one - it puts it in the $ row array you are using and repeats (everything between {and}).
So you select a row, display the results in any form, and then you loop to another row. If there are no more lines to select, the contour ends.
5.You should avoid using commas in the FROM clause in your query. This notation can only be used if the fields joining the tables are obvious (said to be the same), but it's bad practice anyway. The connections between tables must be specified explicitly. In the first request, you only want the header and there is no additional table in your example, so you should only have
SELECT *
FROM Orders
WHERE Orders.Order_ID = 12345
whereas in the second query I understand that you have a Parts table which contains descriptions of the different parts that can be ordered? If so, then the second request should have:
SELECT *
FROM Order_items
JOIN Parts ON Parts.ID = Order_Items.Part_ID
WHEERE Order_Items.Order_ID = 12345
If in your Orders table you have a field for the vendor ID-vendor ID pointing to the Vendors table and the person who placed the order Customer_ID pointing to the Customers table, then the first query would look like this:
SELECT *
FROM Orders
JOIN Suppliers ON Suppliers.ID = Orders.Supplier_ID
JOIN Customers ON Customers.ID = Orders.Customer_ID
WHERE Orders.Order_ID = 12345
Hope this is enough for you to find out further on your own :).
source to share