How to SELECT from MySQL and display results properly with PHP

Question

I am a bit stuck with a problem and need your help to identify the wrong part of code.

I will try to explain is short what I am trying to do. My SQL table looks something like this:

make     model       model_body     variant   
M1       A           ooo            Va1    
M1       B           sss            Va2    
M1       B           sss            Va3    
M1       A           ooo            Va4
M1       A           ooo            Va5    
M1       B           jjj            Va6   
M1       A           www            Va7

I would like to output the table contents in HTML like this:

First, I am trying to list once the contents of model_body column by using SELECT DISTINCT and from there I am trying to list each different value from variant column that matches the same model_body.

A - ooo     Va1
            Va4
            Va5
A - www     Va7

B - sss     Va2
            Va3
B - jjj     Va6

My SQL queries are listed below:

$catalogue_make =  $_GET['m'];

$sql_all_models =  "SELECT * FROM tbl_catalog WHERE make = '$catalogue_make'"; 
$rs_all_models  =  $db -> Execute($sql_all_models);    

$sql_main_model     = "SELECT * FROM tbl_catalog 
                        WHERE make = '".$rs_all_models->fields['make']."' 
                        GROUP BY model_body";
$rs_main_model  =  $db -> Execute($sql_main_model);    

$sql_variant        = "SELECT * FROM tbl_catalog 
                        WHERE model_body = '".$rs_main_model->fields['model_body']."'";
$rs_variant     =  $db -> Execute($sql_variant);

The PHP code I wrote is this one, I tried several different possibilities but always end up with some kind of mismatch:

<?php while (!$rs_main_model->EOF)   { ?>                   
        <div>               
            <div>        
                <h2<?php echo $rs_main_model->fields['model']; ?></h2>
            </div>                                    
            <div>      
                      <?php while (!$rs_variant->EOF) { ?>                                  
                      <?php echo $rs_variant->fields['variant']; ?>
                      <?php $rs_variant->MoveNext(); } ?>
                </div>
        </div>
  <?php $rs_main_model->MoveNext(); } ?>

EDIT This code lists the unique model_body as I want them, however I encounter one problem:

I get the $rs_variant->fields['variant'] listed only for the first model_body. The <div>s for the second, third and so on unique model_bodys are empty. How can I make the loop show the variants for the other matching model_body?

This is what I get now with the second loop (I believe the problem is with the loop itself):

A - ooo     Va1
            Va4
            Va5
A - www     

B - sss     

B - jjj

Thank you!

Please, [don't use `mysql_*` functions in new code](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). *They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation)*. See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which. — Jay Blanchard, Oct 01 '14 at 21:32
You only need one query and one loop for this, especially since you are selecting from the same table every time. — jeroen, Oct 01 '14 at 21:42
Thank you for the response Jay. Actually, this code was suggested by another person and I was trying to implement it in my solution. I see now that this was a bad idea and I have corrected the queries to work with ADOdb. In anycase, it seems that the new queries do the exact same thing so my logic should be wrong somewhere. — BradG, Oct 02 '14 at 08:21

score 0 · Answer 1 · edited May 23 '17 at 11:49

0

Note:

I used ORDER BY for your query.
Compared the model and model_body to the next loop of model and model_body.
More explanations inside the code quoted in /* */
No need to create a complicated nested loop.
Used MySQLi prepared statement instead of deprecated MySQL. Read here to learn more about SQL injections.

Code:

<?php

/* ESTABLISH CONNECTION */

$conn = new mysqli("YourHost", "YourUsername", "YourPassword", "YourDatabaseName"); /* REPLACE THE NECESSARY DATA INSIDE */

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$catalogue_make=$_GET['m'];
$previousmodel="";
$previousbody="";

if($stmt=$con->prepare("SELECT model,model_body,variant FROM tbl_catalog ORDER BY model,model_body WHERE make=?")){

$stmt->bind_param('s',$catalogue_make); /* BIND THE VARIABLE TO THE QUERY */

$stmt->execute(); /* EXECUTE QUERY */

?><table border="1"><?

$stmt->bind_result($model,$modelbody,$variant); /* STORE THE RESULTS TO THIS THREE VARIABLES */

  while($stmt->fetch()){ /* FETCH DATA INTO A LOOP */

    if($previousbody!=$modelbody){ /* COMPARE THE PREVIOUS MODEL BODY TO CURRENT MODEL BODY */

      printf("<tr><td>%s - %s</td><td>%s</td></tr>",$model,$modelbody,$variant);
      $previousmodel=$model;
      $previousbody=$modelbody;

    }

    else { /* IF MODEL BODY AND CURRENT MODEL BODY IS EQUAL, ONLY THE VARIANT TO BE PRINTED */

      printf("<tr><td></td><td>%s</td></tr>",$variant);
      $previousmodel=$model;
      $previousbody=$modelbody;

    } /* END OF ELSE */

  } /* END OF LOOP */

?></table><?php

$stmt->close();

} /* END OF PREPARED STATEMENT */

?>

edited May 23 '17 at 11:49

Community

1
1

answered Oct 02 '14 at 01:22

Logan Wayne

6,001
16
31
49

Thank you for the detailed solution Logan. I actually did change the queries to ADOdb so now the this issue is gone. However, I could not implement your code into my solution, since there is much more code on the original files. In my example above I have only left the core `tags` and structure so that when modifying the other source data will fit properly. – BradG Oct 02 '14 at 08:25
@BradG - Did you changed the credentials in the `$conn` variable? When you tried the code I have provided, what error message did you get? – Logan Wayne Oct 02 '14 at 09:28
Actually, the connection to the DB is managed centrally by one config file so I just have include(files_inclide.php) in every php file. – BradG Oct 02 '14 at 10:00
@BradG What's the name of the variable of your connection? – Logan Wayne Oct 02 '14 at 11:14
@ Logan, this is how I connect to the DB right now: `$db = ADONewConnection('mysql'); $db->Connect(DBHOST,DBUSER,DBPASS,DBNAME) or die("Database not found!");` – BradG Oct 02 '14 at 11:25
@BradG - Your connection still uses deprecated MySQL. Try first the code I have provided, and let us see the problem. Just replace inside the `$conn` the necessary database credentials. – Logan Wayne Oct 02 '14 at 11:40

score 0 · Accepted Answer · answered Oct 20 '14 at 16:38

Here is the solution that I ended up with - if it helps anyone:

<?php while (!$rs_main_model->EOF)   { ?>                   
    <div>               
        <div>        
            <h2<?php echo $rs_main_model->fields['model']; ?></h2>
   <?php $sql_variant = "SELECT * FROM tbl_catalog WHERE model_body = '".$rs_main_model->fields['model_body']."'"; $rs_variant = $db -> Execute($sql_variant); ?>
        </div>                                    
        <div>      
                  <?php while (!$rs_variant->EOF) { ?>           
                  <?php echo $rs_variant->fields['variant']; ?>
                  <?php $rs_variant->MoveNext(); } ?>
            </div>
    </div>
<?php $rs_main_model->MoveNext(); } ?>

How to SELECT from MySQL and display results properly with PHP

2 Answers2

Note: