Hello. I have two tables, the itinerary table and location table.

The itinerary table consists of all the itinerary:

  • A - B
  • B - A
  • B - C
  • C - B
  • C - A
  • A - C

The user will select from the itinerary table then it will be saved in the location table:

  • A - B
  • B - A

Now, I want to display all the data in the itinerary table in an array of checkbox using MySQL query. The query will check if the itinerary data exist in the location table. If it does exist, it will echo the checkbox with the attribute checked = 'yes'. If it doesn't, then it'll simply echo the checkbox without the checked attribute.

I can do this but with some minor error, with this code:

$result=mysql_query("select * from itinerary where aircraft = '$_SESSION[rtyp]' group by location");
    while($row=mysql_fetch_array($result))
    {
        $pr=$row['icode'];
        $n=$row['location'];
        $l=$row['btime'];
        $res=mysql_query("select * from location where reservno = '$_SESSION[rno]'") or die(mysql_error());
        while($row1=mysql_fetch_array($res))
        {
            $loc=$row1['location'];
            if($n==$loc)
            {
                echo"<table border=1 align=center width=250>
                <tr><td width=15>
                <input type='checkbox' value='$n -> $l' checked='yes'>
                </td><td>$n</td><td width=5>$l</td></tr>
                </table>";

                $t=$_POST['prod'];
            }
        elseif($n!=$loc)
        {
            echo"<table border=1 align=center width=250>
            <tr><td width=15>
            <input type='checkbox' value='$n -> $l'>
            </td><td>$n</td><td width=5>$l</td></tr>
            </table>";

            $t=$_POST['prod'];
        }
    }
    } 

But it echoes each itineraries twice as is it loops around the result of the second query.

*** A - B**
* A - B
* B - A
*** B - A**
* B - C
* B - C
* C - B
* C - B
* C - A
* C - A
* A - C
* A - C

(the bold list identifies that the checkbox is checked)

How can I optimize this? I don't know if I'm doing something wrong in either my MySQL or PHP code but I'll really appreciate some help with MySQL and PHP code. I'm at my wit's end now. Please help me and point me to how I can achieve the desired result. Thanks a lot!

Member Avatar for LastMitch

I don't know if I'm doing something wrong in either my MySQL or PHP code but I'll really appreciate some help with MySQL and PHP code.

Can you post your 2 tables?

Does table 1 and table 2 have a column in common?

If so, you can join the columns from the 2 tables.

It's very confusing just show letters.

No. The only common field in both tables are the location field and it is unique. The location are places, for example Japan - Philippines, Philippines - Japan, etc. I just used A - B 'cause it's much shorter.

Member Avatar for LastMitch

Try this (Not sure how it will turn out):

$result=mysql_query("select * from itinerary where aircraft = '$_SESSION[rtyp]' group by location");
$res=mysql_query("select * from location where reservno = '$_SESSION[rno]'") or die(mysql_error());

if (($r=mysql_query($result)) && ($r1=mysql_query($res))) {
$pr=$row['icode'];
$n=$row['location'];
$l=$row['btime'];

while (($row=mysql_fetch_assoc($r))||($row1=mysql_fetch_assoc($r1))) {
$loc=$row1['location'];

if($n==$loc){
echo"<table border=1 align=center width=250>
<tr><td width=15>
<input type='checkbox' value='$n -> $l' checked='yes'>
</td><td>$n</td><td width=5>$l</td></tr>
</table>";

$t=$_POST['prod'];

}elseif($n!=$loc){

echo"<table border=1 align=center width=250>
<tr><td width=15>
<input type='checkbox' value='$n -> $l'>
</td><td>$n</td><td width=5>$l</td></tr>
</table>";

$t=$_POST['prod'];
}
}
} 

What results did you get?

Got some error and fixed it. I edited your code to this:

$result="select * from itinerary where aircraft = '$_SESSION[rtyp]' group by location";
    $res="select * from ptr where reservno = '$_SESSION[rno]'";
    if (($r=mysql_query($result)) && ($r1=mysql_query($res))) {

    while (($row=mysql_fetch_assoc($r))||($row1=mysql_fetch_assoc($r1))) {
    $pr=$row['icode'];
    $n=$row['location'];
    $l=$row['btime'];
    $loc=$row1['location'];
    if($n==$loc){
    echo"<table border=1 align=center width=250>
    <tr><td width=15>
    <input type='checkbox' value='$n -> $l' checked='yes'>
    </td><td>$n</td><td width=5>$l</td></tr>
    </table>";
    $t=$_POST['prod'];
    }elseif($n!=$loc){
    echo"<table border=1 align=center width=250>
    <tr><td width=15>
    <input type='checkbox' value='$n -> $l'>
    </td><td>$n</td><td width=5>$l</td></tr>
    </table>";
    $t=$_POST['prod'];
    }
    }
    } 

But it's not doing what is desired. It is echoing all the itineraries from itinerary table and echoing the itineraries at last part but not checked.

Member Avatar for LastMitch

But it's not doing what is desired. It is echoing all the itineraries from itinerary table and echoing the itineraries at last part but not checked.

So techically you want something like this 2 table switching back and forward:

Japan - Philippines 
Philippines - Japan
America - Britain
Britain - America

I didn't understand your sequence because it was not bold or wasn't clear:

Is this the sequence you want or not want:

A - B
A - B
B - A

B - A
B - C
B - C
C - B

C - B
C - A
C - A
A - C

A - C

The itinerary table contains all the itineraries while the location table contains the saved itineraries the user pre-selected.

So if itinerary table contains this data:

  • Japan - Philippines
  • Philippines - Japan
  • London - Korea
  • Korea - London

And the location table contains these data (which was pre-selected and saved)

  • Japan - Philippines
  • Philippines - Japan

Then, the result of the code should be an array of checkbox like this (bold indicates checked='yes' attribute of checkbox)

  • Japan - Philippines
  • Phillipines - Japan
  • London - Korea
  • Korean - London
Member Avatar for LastMitch

OK. Stay with your code and add this (I change the value to this [ ] so when you check yes it will match with your location (I hope that make sense)):

$result=mysql_query("select * from itinerary where aircraft = '$_SESSION[rtyp]' group by location");

while($row=mysql_fetch_array($result)){
$pr=$row['icode'];
$n=$row['location'];
$l=$row['btime'];

$res=mysql_query("select * from location where reservno = '$_SESSION[rno]'") or die(mysql_error());

while($row1=mysql_fetch_array($res)){
$loc=$row1['location'];

if(isset($row['location']) && $row1['location'] == 'Yes'){

echo"<table border=1 align=center width=250>
<tr><td width=15>
<input type='checkbox' value='local[]' checked='Yes'>
</td><td>$n</td><td width=5>$l</td></tr>
</table>";
$t=$_POST['prod'];

}elseif($n!=$loc){

echo"<table border=1 align=center width=250>
<tr><td width=15>
<input type='checkbox' value='local[]'>
</td><td>$n</td><td width=5>$l</td></tr>
</table>";

$t=$_POST['prod'];

$local = $_POST['local'];

if($n = count($local)){
for($i=0; $i < $n; $i++){
echo $local[$i];
}
}
}
}
} 

It outputs all the itineraries from itinerary table but there is no checked checkbox.

Member Avatar for LastMitch

It outputs all the itineraries from itinerary table but there is no checked checkbox.

I think I write it wrong in the input tags.

It should be

<input type='checkbox' value='location[]' checked='Yes'>

The code is bit hard to modify because in order for this to work it has to have this [ ] in the input tags and also when you check the valve it will match the valve and it will be bold. I'm not sure this will work either:

Try this:

$result=mysql_query("select * from itinerary where aircraft = '$_SESSION[rtyp]' group by location");
while($row=mysql_fetch_array($result))
{
$pr=$row['icode'];
$n=$row['location'];
$l=$row['btime'];
$res=mysql_query("select * from location where reservno = '$_SESSION[rno]'") or die(mysql_error());
while($row1=mysql_fetch_array($res))
{
$loc=$row1['location'];
if(isset($n) && $loc == "Yes")
{
echo"<table border=1 align=center width=250>
<tr><td width=15>
<input type='checkbox' value='location[]' checked='Yes'>
</td><td>$n</td><td width=5>$l</td></tr>
</table>";
$t=$_POST['prod'];
}
elseif($n!=$loc)
{
echo"<table border=1 align=center width=250>
<tr><td width=15>
<input type='checkbox' value='location[]'>
</td><td>$n</td><td width=5>$l</td></tr>
</table>";
$t=$_POST['prod'];
}
}
} 

It outputs all the itineraries from itinerary table twice and still there is no checked checkbox.

Achieved it using this code:

$result=mysql_query("select * from itinerary where aircraft = '$_SESSION[rtyp]' group by location");
    while($row=mysql_fetch_array($result))
    {
        $pr=$row['icode'];
        $n=$row['location'];
        $l=$row['btime'];
        $res=mysql_query("select * from location where reservno = '$_SESSION[rno]'") or die(mysql_error());
        while($row1=mysql_fetch_array($res))
        {
            $loc=$row1['location'];
                if($n==$loc){$ctr="true"; break;}
                else{$ctr="false";}

        }
        if($ctr=="true"){
                echo"<table border=1 align=center width=250>
                <tr><td width=15>
                <input type='checkbox' value='$n -> $l' checked='yes'>
                </td><td>$n</td><td width=5>$l</td></tr>
                </table>";

                $t=$_POST['prod'];
            }
            else
            {
                echo"<table border=1 align=center width=250>
                <tr><td width=15>
                <input type='checkbox' value='$n -> $l'>
                </td><td>$n</td><td width=5>$l</td></tr>
                </table>";

                $t=$_POST['prod'];
            }

    }
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.