Wrong id keeps getting looped.

[bambinou1980]

Hello,

 

I cannot work out this one, I have this select statement

 

$sql = "SELECT o.id,
               o.cust_order_id,
               o.cust_company,
               o.due_date,
               o.product,
               o.quantity,
               o.price,
               o.requirements,
               o.cust_order_total,
               o.order_status,
               o.reseller_earnings,
               o.resellers_id,
               r.reseller_name,
               r.reseller_surname,
               r.id
FROM orders as o, resellers as r
WHERE  o.resellers_id = r.id ORDER BY due_date DESC";

 

 

In my html table I would like to output the "o.id" but each time the "r.id" is being output, any idea why please?

I tried to add instead "$id                = $row["o.id"] but no luck. I also tried "$id                = $row["irders.id"];

 
 
 <?php
                // Attempt select query execution
                if($result = mysqli_query($connection, $sql)){
                if(mysqli_num_rows($result) > 0){
                echo "<table class='table table-striped'>";
                echo "<tr>";
                echo "<th class='col-md-1 text-center'>Order ID</th>";
                echo "<th class='col-md-1 text-center'>Company</th>";
                echo "<th class='col-md-1 text-center'>Due Date</th>";
                echo "<th class='col-md-1 text-center'>Product</th>";
                echo "<th class='col-md-1 text-center'>Unit Price</th>";
                echo "<th class='col-md-1 text-center'>Quantity</th>";
                echo "<th class='col-md-1 text-center'>Order Total</th>";
                echo "<th class='col-md-5 text-center'>Requirements</th>";
                echo "<th class='col-md-1 text-center'>Reseller</th>";
                echo "<th class='col-md-1 text-center'>Reseller Earnings</th>";
                echo "<th class='col-md-1 text-center'>Order Status</th>";
                echo "<th class='col-md-1 text-center'></th>";

                while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
                $id                = $row["id"];<---------------------------------this is pulling the r.id instead of o.id
                $cust_order_id      = $row["cust_order_id"];
                $cust_company       = $row["cust_company"];
                $due_date           = $row["due_date"];
                $product            = $row["product"];
                $quantity           = $row["quantity"];
                $price              = $row["price"];
                $requirements       = $row["requirements"];
                $cust_order_total   = $row["cust_order_total"];
                $resellers          = $row["reseller_name"] . ' ' . $row["reseller_surname"];
                $reseller_earnings  = $row["reseller_earnings"];
                $order_status       = $row["order_status"];
                
                
                echo "<tr class='text-center'>";
                echo "<td>" . htmlspecialchars($cust_order_id) . "</td>";
                echo "<td>" . htmlspecialchars($cust_company) . "</td>";
                $new_date = date("d-m-Y", strtotime($due_date));
                echo "<td>" . $new_date . "</td>";
                echo "<td>" . htmlspecialchars($product) . "</td>";
                echo "<td>" . htmlspecialchars($price) . "</td>";
                echo "<td>" . htmlspecialchars($quantity) . "</td>";
                echo "<td>" . htmlspecialchars($cust_order_total) . "</td>";
                echo "<td>" . htmlspecialchars($requirements) . "</td>";
                echo "<td>" . htmlspecialchars($resellers) . "</td>";
                echo "<td>" . htmlspecialchars($reseller_earnings) . "</td>";
                echo "<td>" . htmlspecialchars($order_status) . "</td>";
                echo "<td><a class='btn btn-default' href='update-orders.php?order=$id' role='button'>Edit</a><a class='btn btn-danger' href='delete-orders.php?order=$id' onclick=\"return confirm('Are you sure you want to delete this order?');\" role='button'>Delete</a></td>";
                echo "</tr>";
                     }
                echo "</table>";
               // Close result set
               mysqli_free_result($result);
              }else{
              echo "Not Results to Show, get more orders!!";
                }}
                ?>

 

Thank you.

[cyberRobot]

Try using a column alias.

SELECT o.id AS orderID,

The alias would be used in your loop like this:

$id = $row['orderID'];

[Psycho]

Just to add to cyberRobot's response. If you reference the values in the result set in an associative array, there can only be 'one' value for any specific key in the array. So, you either need to alias different fields that use the same name or you can reference the data with a numerically based index. I would go with the former as cyberRobot suggests.

 

Also, if you have such a problem, that might be an indication that you need to use more unique names (e.g. order_id & resellers_id). This way, you can use the same field name as the primary key in one table and the foreign key in another. It may seem more "wordy" and superfluous, but your queries will be much easier to read when you have many JOINs. Also, there are some MySQL functions that you can't use otherwise, such as USING()

[mac_gyver]

and since you are already selecting resellers_id and you know that o.resellers_id = r.id from the join condition, why are you selecting r.id at all?

Edited August 25, 2015 by mac_gyver

[bambinou1980]

and since you are already selecting resellers_id and you know that o.resellers_id = r.id from the join condition, why are you selecting r.id at all?

 

Yes you are right, I have removed it.

 

 

Just to add to cyberRobot's response. If you reference the values in the result set in an associative array, there can only be 'one' value for any specific key in the array. So, you either need to alias different fields that use the same name or you can reference the data with a numerically based index. I would go with the former as cyberRobot suggests.

 

Also, if you have such a problem, that might be an indication that you need to use more unique names (e.g. order_id & resellers_id). This way, you can use the same field name as the primary key in one table and the foreign key in another. It may seem more "wordy" and superfluous, but your queries will be much easier to read when you have many JOINs. Also, there are some MySQL functions that you can't use otherwise, such as USING()

 

Yes excellent idea, it took me some time but I have actually changed all the ids of all my tables and changed all the ids in all my CRUDS , now each tables has it's own id name, I won't make the same mistake in the future, very good point.

 

I have a small problem, imagine that sometime an order may or may not have a reseller added to the order form, therefore soemetime, the form will register a reseller id and sometime will register 0 as no resellers will be chosen from the dropdown menu.

 

What I am experiencing now, is that with the below query, if there are no resellers and the id is set to "0", I do not see the results in the select query.....

 

$sql = "SELECT o.orders_id,
               o.cust_order_id,
               o.cust_company,
               o.due_date,
               o.product,
               o.quantity,
               o.price,
               o.requirements,
               o.cust_order_total,
               o.order_status,
      o.reseller_earnings,
      o.orders_resellers_id,
               r.reseller_name,
               r.reseller_surname
FROM orders as o, resellers as r
WHERE  o.orders_resellers_id = r.resellers_id ORDER BY due_date DESC";​

 

 

I remember once in another thread, one of you proposed to use something called a JOIN to go around this problem.

 

Could you please show me a quick example on the above query how to show the results by force of the "orders" table even if there are no resellers found with the id of "0".

 

 

Thank you so much, lucky you are here!!I would have been totally stuck long time ago, I am slowly understanding the magic of MySQL...but Oh Boy....it is not an easy task to learn all of this after a hard day of work that has nothing to do with coding......

[Psycho]

Well, you are using an INNER JOIN (or just JOIN) in a way by including both tables and using this in your WHERE clause

 

WHERE  o.orders_resellers_id = r.resellers_id

 

Personally, I never use that type of construct to get data from associated tables. I would always use a proper JOIN for many reasons. For one it makes the criteria for associating the records much more apparent. Plus there are many features you can't use without a proper JOIN. Your original query would work the same as this

 

 

FROM orders AS o
JOIN r.resellers AS r
  ON o.orders_resellers_id = r.resellers_id

 

The normal JOIN will return a result set for all records where a JOIN can be made. In this case, if there are any records in orders that does not have at least one corresponding record in resellers OR if there are any records in resellers that do not have a corresponding record in orders they will not be returned. In this case, you want ALL the records from the orders table even if there are no records in the resellers table. Based on the current query structure you would use a LEFT JOIN. That means return ALL matching records from the LEFT table (i.e. first table - orders) and JOIN any matching records from the right table (resellers).

 

 

$sql = "SELECT o.orders_id, o.cust_order_id, o.cust_company, o.due_date,
               o.product, o.quantity, o.price, o.requirements, o.cust_order_total,
               o.order_status, o.reseller_earnings, o.orders_resellers_id,
               r.reseller_name, r.reseller_surname
 
FROM orders AS o
LEFT JOIN resellers as r
  ON o.orders_resellers_id = r.resellers_id
 
ORDER BY due_date DESC";​

 

Take a look at this page for some examples of JOIN types: http://www.sitepoint.com/understanding-sql-joins-mysql-database/

 

Also, I see you named the foreign key in the orders table as "o.orders_resellers_id". I would suggest naming it "o.resellers_id" - exactly the same as it is in the resellers table. That makes it explicit that it is a primary/foreign key in those two tables. Plus, as stated previously, there are some features where using the same name simplifies the logic: e.g. USING(). You could use this for example

 

FROM orders AS o
LEFT JOIN resellers as r
  USING(resellers_id)

[bambinou1980]

Hi Psycho,

 

Thank you so much.

 

I loved this " LEFT table (i.e. first table - orders) and JOIN any matching records from the right table (resellers)." It makes it very easy to understand.

 

Regarding the ids....ohh...I have changed all the id names yesterday in all my table and pages according to the a post I read that was going again the fact that I should not use the same table name ids.

 

No worry, or now I will leave it and in my next project I will follow those rules.

 

Quick question please, what difference does it makes to use foreign key vs joins please? At the moment I prefer to use joins because I always get warning that I cannot edit a parent table via a child table but would the foreign key structure be better than JOINS?

 

Thank you

[Barand]

JOIN and FOREIGN KEY are not mutually exclusive, it isn't a case of one or the other.

+------------------+          +-----------------+
|  reseller        |          |    order        |
+------------------+          +-----------------+
| reseller_id   PK | ---+     | order_id     PK |
| reseller_name    |    |     | order_total     |
| reseller_surname |    +---< | reseller_id  FK |
+------------------+          +-----------------+

In the tables above, reseller_id is is the primary key (PK) of the reseller table. It also appears in the order table to relate an order to the reseller. As it is the PK of one table appearing in another table it is, by definition, a FOREIGN KEY.

 

When querying the tables you (normally) JOIN the tables using this PK --> FK relationship.

 

You also have the option of applying FOREIGN KEY CONSTRAINTS on a table. If you do this enforces "referential integrity" so you cannot add an order with a reseller_id that does not exist in the reseller table, or you cannot delete a reseller record that has related orders in the order table.

[bambinou1980]

Thank you Barand,

 

Actually what I meant is not exactly this, I just wanted to know why sometime we create an actual foreign key in the database and why sometime it is just done via the query like this guy here explains:

http://stackoverflow.com/questions/2947440/foreign-keys-vs-joins

 

 

Here is said:

​Joins are defined using foreign keys only. Of course, you might not define the foreign key in the database. Using foreign key will better the performance (provided the foreign key selection is right).

 

Now why would create an actual foreign key in the database itself rather than just in the query add better performance to the database please? Any idea?

​

Thank you,

​

Ben​

 

​

[Barand]

 

Now why would create an actual foreign key in the database itself rather than just in the query add better performance to the database please? Any idea?​

 

​

 

I gave you the answer to that:

 

 

You also have the option of applying FOREIGN KEY CONSTRAINTS on a table. If you do this enforces "referential integrity" so you cannot add an order with a reseller_id that does not exist in the reseller table, or you cannot delete a reseller record that has related orders in the order table.

[bambinou1980]

Thanks Barand for the explanations!