issue showing results - 1 row keeps alternating

[Dicko_md]

Hi

 

I have 2 tables that I am searching infor for. One holds the phone info and the other the repairs that are available.

 

I have the code below and it displays on the webpage in a table for the phone information and then another table the repairs but they seem to be separate tables and headers. inbetween them is the phone and the details which separates the repairs but its not in a table.

 

Hopefully the code below will explain better.

 

What I would like it the phone details in one table and then a table full of all the repairs (I am not bothered about a new heading for each repair).

$result = mysql_query("SELECT * FROM phone, phonerepairs WHERE phone.model_no='".$_POST['model']."' AND phone.phone = phonerepairs.phone");

while($row = mysql_fetch_array($result))
{
echo '<td width="70" align="center"><img src="'.$row['icon'].'" width="66"></td>';
echo '<td width="100" align="center">'.$row['model_no'].'</td>';
echo '<td width="130" align="center">'.$row['model'].'</td>';
echo '<td width="50" align="center">'.$row['year'].'</td>';
echo '<td width="150" align="center">'.$row['capacity'].'</td>';
echo '</tr>';
echo '</table><br>';
echo '</p>';
echo '<p>';
echo '<div id="pageheading"><h3><div id="title">Repairs we carry out for '.$row['model'].'</div></h3></div>';
echo '<table width="99%" cellpadding="5" cellspacing="0" border="1">';
echo '</p>';
echo '<p>';
echo '<tr>';
echo '<td align="center"><strong>Image</strong></td>';
echo '<td align="center"><strong>Fault</strong></td>';
echo '<td align="center"><strong>Repair</strong></td>';
echo '<td align="center"><strong>Cost</strong></td>';
echo '</tr>';
echo '<tr>';
echo '<td width="70" align="center"><img src="'.$row['icon'].'" width="66"></td>';
echo '<td width="100" align="center">'.$row['fault'].'</td>';
echo '<td width="130" align="center">'.$row['description'].'</td>';
echo '<td width="50" align="center">&pound'.$row['cost'].'</td>';
echo '</tr>';
echo '</table><br>';

Thanks in advance 

 

Martyn

[mac_gyver]

you would use a variable to remember the last phone value (initialize to a value that won't ever exist as data, such as a null) and use this variable to test when the phone changes inside your while(){} loop. when the value changes, close out any previous phone section, output the new phone information, and set the variable to the new phone value.

 

even though your query is searching for a single model, this method will work for any number of models your query matches.

 

pseudo code -

$last_heading = null; // variable to remember and detect when the heading changes

while(...){
    $new_heading = $row['some_column_that_you_want_to_do_something_only_once_when_it_changes'];
    // detect when the heading value changes
    if($last_heading != $new_heading){
        // detect if it's not the first one (not the initial value)
        if($last_heading != null){

            // there is a previous data section, close it here...
            
        }
        $last_heading = $new_heading; // remember the new heading value

        // output a heading section and start a new data section here...
        
    }
    
    // output the data under each heading here...

}

// close out the last data section if there is one
if($last_heading != null){

    // there is a previous data section, close it here...
    
}

[Dicko_md]

Hi

 

Thanks for your reply.

 

Would I be best keeping the 2 tables separate and use your code above ? I have one column in each table that is the same ie phone so this could be the stored variable.

 

How would I get my code into your suggestion to output the available repairs for a particular phone. ?

 

Thanks again

 

Martyn

[Dicko_md]

Any one able help to show me how to use the above code with my code ?

 

Thanks

 

Martyn

[mac_gyver]

what have you tried? if you haven't tried anything at all, there's nothing to help you with, since you don't have any attempted code to post along with the symptom or error you got from that code.

 

one of the great things about software and programming is you get immediate feedback as to if something you thought would work did or not, which is pretty much how learning works, and since it's software, you can freely change it and try other things that you think will work based on the feedback you got from your earlier attempts.

 

if all you are doing is sitting around waiting for someone to post a copy/paste solution, you will have a long wait, because that's not how programming help forums work.

 

short-answer: the suggested method requires one php variable. it gets initialized before the start of your looping code, it gets tested to determine what the code should do, and it gets a new value stored (remembered) in it.

[Dicko_md]

Hi

 

Sorry I wasnt trying to get the code for free without trying. I was genuinely not sure how to incorporate this into your code you shared.

 

I have probably gone about it the wrong or should I say long way round but it works below after playing with my code. 

 

 

$result = mysql_query("Select DISTINCT phone.model, phone.model_no, phone.icon, phone.year, phone.capacity From phonerepairs INNER JOIN phone ON phonerepairs.phone=phone.phone WHERE phone.model_no='".$_POST['model']."'");

 

$result2 = mysql_query("Select * From phonerepairs INNER JOIN phone ON phonerepairs.phone=phone.phone WHERE phone.model_no='".$_POST['model']."'");

 

while($row = mysql_fetch_array($result))

{

echo '<tr>';

echo '<td width="70" align="center"><img src="'.$row['icon'].'" width="66"></td>';

echo '<td width="100" align="center">'.$row['model_no'].'</td>';

echo '<td width="130" align="center">'.$row['model'].'</td>';

echo '<td width="50" align="center">'.$row['year'].'</td>';

echo '<td width="150" align="center">'.$row['capacity'].'</td>';

echo '</tr>';

}

 

echo '</table><br></p>';

 

while($row = mysql_fetch_array($result2))

{

echo '<p>';

echo '<div id="pageheading"><h3><div id="title">Repairs we carry out for '.$row['model'].'</div></h3></div>';

echo '<table width="99%" cellpadding="5" cellspacing="0" border="1">';

echo '</p>';

echo '<p>';

echo '<tr>';

echo '<td align="center"><strong>Image</strong></td>';

echo '<td align="center"><strong>Fault</strong></td>';

echo '<td align="center"><strong>Repair</strong></td>';

echo '<td align="center"><strong>Cost</strong></td>';

echo '</tr>';

echo '<tr>';

echo '<td width="70" align="center"><img src="'.$row['icon'].'" width="66"></td>';

echo '<td width="100" align="center">'.$row['fault'].'</td>';

echo '<td width="130" align="center">'.$row['description'].'</td>';

echo '<td width="50" align="center">&pound'.$row['cost'].'</td>';

echo '</tr>';

echo '</table><br>';

 

}

 

mysql_close($con);

?>

 

</p>

 

The only nicety thing I would like if possible is to alternate the table heading on each fix available if its a quick fix.

 

Thanks

 

Martyn

[Ch0cu3r]

Splitting your code into two query is inefficient. Your original code you posted is fine, all you needed to do is implement the while loop provided by mac_gver.

 

 

I will you step through the 6 (simple) changes  you need to make to be able to implement it into your code

 

1.  The first line to change is

while(...){

Here you need to replace the  ...  with your  mysql_fetch_*()  function. Like so

while($row = mysql_fetch_assoc($result)){

2. The second change is this line

$new_heading = $row['some_column_that_you_want_to_do_something_only_once_when_it_changes'];

Replace   some_column_that_you_want_to_do_something_only_once_when_it_changes  with the field you don't want to be repeated. In your case you don't want the phones model to be repeated so we'll use the  model  field here.

$new_heading = $row['model'];

This variable is used to output a new table for listing the new phones information and its repairs table when the phones model changes

 

3 & 6. Where you see this if statement

        if($last_heading != null){

            // there is a previous data section, close it here...
            
        }

You replace the  comment (  // there is a previous data section, close it here...  ) with the HTML for closing the  phone repairs and phone information sections. Example

echo '
    </table> <!-- closes the repairs table -->

  </tr>
</table> <!-- closes the phone information table -->';

This code is only executed when the model of the phone changes!

 

4. Replace

// there is a previous data section, close it here...

With the HTML for listing the phones information (icon, model, model_no, year, capacity). You will also open a table for listing the repairs for that model. Example

echo '
<table>
  <tr>
     ... list the phones information ...
  </tr>
  <tr>
    <table>
      <tr>
        ... create icon, fault, description and cost headings ONLY ...
      </tr> <!-- open the table but DO NOT CLOSE IT -->
';

This code is only executed when the phone model has changed. We create a new table for listing the phones information and opening a new table for listing the repairs.

 

 

5. Replace

 // output the data under each heading here...

Here you will create a new table row outputting these fields: icon, fault, description and cost from the database

echo '
         <tr>
            ... output row for repair information (icon, fault, description, cost)
         </tr>';

This code is used for listing the repairs for the current model

 

 

Those are only changes you need to make. I have explained what changes are required with examples. You should now be able to replace your while loop with mac_gver's.

 

 

The next challenge for you is to replace mysql_* functions with either PDO or MySQLi. The mysql_* functions are deprecated which means they are no longer supported and could be removed from future versions of PHP. 

Edited July 7, 2014 by Ch0cu3r