SQL query returning only 1 row

Question

I am working on a php project where users can select a make and model of a car from a form and this will trigger the search for products that apply for that particular combination. When I run the query in SQL, and there are multiple products, I get multiple rows. but when I try to display those results using PHP I only get 1 row. Do you have an idea what I am doing wrong? here's the code:

$query1 = "SELECT * FROM partmakes WHERE MAKE = '$_POST[make]' and MODEL ='$_POST[model]'";
$results1 = mysqli_query($cnx, $query1);
$row1 = mysqli_fetch_assoc($results1);


$query2 = "SELECT * FROM `products` WHERE `PARTNO`= '$row1[PARTMAKE1]' OR (`PARTNO`= 
'$row1[PARTMAKE2]' OR `PARTNO`= '$row1[PARTMAKE3]' OR `PARTNO`= '$row1[PARTMAKE4]');";

$results2 = mysqli_query($cnx, $query2);

echo "These are the products for your car:<br />";
while ($row2 = mysqli_fetch_assoc($results2)){
    extract($row2);
    echo "$row2[PARTNO]<br />";
}

Thanks a lot for your help

edit - content from OP's file http://standtek.com.mx/test/screenshots.docx in the comments

So the user will select from a drop down the make and the model of the car. The first query looks for the make and model in the table: enter image description here

Then I use the information from the PARTMAKE columns as reference for the second query

Here’s an example of the query in phpmyadmin and it shows 4 rows: enter image description here

My current script displays only 1 row for some reason: enter image description here

This is what I want displayed in the results: enter image description here

Instead of 2 separate queries, you could do this in a `JOIN` query. Something like - `SELECT * FROM products JOIN partmakes ON (products.PARTNO= partmakes.PARTMAKE1 OR products.PARTNO= partmakes.PARTMAKE2 OR products.PARTNO = partmakes.PARTMAKE3 OR products.PARTNO = partmakes.PARTMAKE4) WHERE partmakes.MAKE = '$_POST[make]' AND partmakes.MODEL ='$_POST[model]'`. note, make sure to sanitize your user data. — Sean, Jul 07 '15 at 17:16
This gives me 1 row with the rest of the PARTMAKES at the end of the row. What I am looking for is to get individual rows for each PARTMAKE. Something like this: PARTNO DESC PRICE 1 Something 10.50 2 other stuff 7.50 — Eduardo, Jul 07 '15 at 18:39
Can you provide some sample data from your 2 tables, and show your desired result. — Sean, Jul 07 '15 at 19:05
Yes, thanks. Please take a look at the following link: [link](http://standtek.com.mx/test/screenshots.docx) There I put some screenshots of the DB and the desired results — Eduardo, Jul 07 '15 at 19:22
Next time, add your notes and screenshots to your question, not as a `docx` file link in a comment. I have added the `docx` contents to your question and will post an answer shortly. — Sean, Jul 07 '15 at 20:04

score 0 · Accepted Answer · answered Jul 07 '15 at 20:17

0

Use a JOIN query and you could return all the rows -

$query = "SELECT products.* 
          FROM products
          JOIN partmakes 
          ON (
             products.PARTNO = partmakes.PARTMAKE1 
             OR 
             products.PARTNO = partmakes.PARTMAKE2 
             OR 
             products.PARTNO = partmakes.PARTMAKE3 
             OR
             products.PARTNO = partmakes.PARTMAKE4) 
          WHERE partmakes.MAKE = '$_POST[make]' 
          AND partmakes.MODEL ='$_POST[model]'";

$results = mysqli_query($cnx, $query);

echo "These are the products for your car:<br />";

echo "<table><tr><th>PARTNO</th><th>TITLE</th><th>PRICE</th></tr>";

while ($row = mysqli_fetch_assoc($results)){
    echo "<tr><td>{$row[PARTNO]}</td><td>{$row[TITLE]}</td><td>{$row[PRICE]}</td></tr>";
}
echo "</table>";

Make sure to sanitize your $_POST user data.

You can see the JOIN query working in sqlFiddle - http://sqlfiddle.com/#!9/754feb/4

answered Jul 07 '15 at 20:17

Sean

12,443
3
29
47

Thanks a lot for all the effort, I appreciate it.. however it still shows 1 row :( I know that the sqlfiddle example is showing all rows but when you try the queries in phpmyadmin it only returns 1 row. And php does not show any errors or warnings, just 1 row – Eduardo Jul 07 '15 at 20:36
There might be some issue with your data then. For example, there might be some leading or trailing white spaces, so a basic `=` will not match. You will need to look at the 3 rows that don't show up, and figure out why they are not being returned. – Sean Jul 07 '15 at 20:43
After some time reviewing the data the solution was to get rid of extra white spaces like you suggested. Thank you very much for your time and help! – Eduardo Jul 08 '15 at 01:02

SQL query returning only 1 row

1 Answers1