[SOLVED] Display data from 2 mysql table with single query help

[MP145]

Hi guys,

 

Im trying to display data from 2 tables using a single query. I'm not sure if this is the correct method but in 1-table 'ppricing' the data are all single row for the '$newd' variable where else in another table 'pdetails' there are multiple rows for the '$newd' variable.

 

With the code below, it loops table 'ppricing' data multiple times. I just need the data from the table 'pdetails' to loop.

 

Can this be done or do  i need to run 2 seperate queries?

 

Thank You.

 

<?php

        if(isset($_GET['package'])){
        $id = $_GET['package'];
        $newd=base64_decode($id);
        mysql_connect($dbhost,$dbuser,$dbpass) or die("Error:: can't connect to database".mysql_error());
        $db = mysql_select_db("$dbname") or die("Unable to select db");
        $data = mysql_query("SELECT * FROM ppricing,pdetails WHERE ppricing.ptitle = '$newd' AND pdetails.pktitle = '$newd'") or die(mysql_error());
        $num=mysql_numrows($data);

        if ($num==0) {
        echo "Check Back Soon";
        } else {
        while($prinfo = mysql_fetch_array( $data ))
        {

        echo '<div id="pricinginc">';
        echo '<div id="ptincl">'.$prinfo['ptitle'].'</div>';
        echo '<div id="ptincr">'.$prinfo['psprice'].'</div>';
        echo '<div id="ptincra">'.$prinfo['peprice'].'</div>';
        echo '<div id="ptincl1">';
        echo '<div class="prclist">';
        echo '<ul>';
        echo '<li>'.$prinfo['listitem'].'</li>';
        echo '</ul>';
        echo '</div>';
        echo '</div>';
        echo '<div id="clear"><!-- --></div>';
        echo '<div id="ptincl2">'.$prinfo['psummary'].'</div>';
        echo '<div id="ptincl2pic">'.$prinfo['ppic'].'</div>';
        echo '</div>';
        }
        }
        }
        else {
        echo "Blah..blah..blah";
        }
?>

[vbnullchar]

use JOIN

 

SELECT column_name(s)

FROM table_name1

INNER JOIN table_name2

ON table_name1.column_name=table_name2.column_name

 

can you post the table structure?

[MP145]

Thanks vbnullchar for the reply. 

 

I went around and split the queries like this :

<?php

        if(isset($_GET['package'])){
        $id = $_GET['package'];
        $newd=base64_decode($id);
        mysql_connect($dbhost,$dbuser,$dbpass) or die("Error:: can't connect to database".mysql_error());
        $db = mysql_select_db("$dbname") or die("Unable to select db");
        $data1 = mysql_query("SELECT * FROM ppricing WHERE ptitle = '$newd'") or die(mysql_error());
        $data2 = mysql_query("SELECT * FROM pdetails WHERE pktitle = '$newd'") or die(mysql_error());
        $num1=mysql_numrows($data1);
        $num2=mysql_numrows($data1);

        if ($num1==0) {
        echo "Check Back Soon";
        } else {
        while($prinfo1 = mysql_fetch_array( $data1 ))
        {

        echo '<div id="pricinginc">';
        echo '<div id="ptincl">'.$prinfo1['ptitle'].'</div>';
        echo '<div id="ptincr">Start Price: '.$prinfo1['psprice'].'</div>';
        echo '<div id="ptincra">End Price: '.$prinfo1['peprice'].'</div>';
        echo '<div id="ptincl1">';
        echo '<div class="prclist">';
        echo '<ul>';
        if ($num2==0) {
        echo "Check Back Soon";
        } else {
        while($prinfo2 = mysql_fetch_array( $data2 ))
        {
        echo '<li>'.$prinfo2['listitem'].'</li>'; // Only here the result is multiple rows . The rest is single rows
        }
        }
        echo '</ul>';
        echo '</div>';
        echo '</div>';
        echo '<div id="clear"><!-- --></div>';
        echo '<div id="ptincl2">'.$prinfo1['psummary'].'</div>';
        echo '<div id="ptincl2pic">'.$prinfo1['ppic'].'</div>';
        echo '</div>';
        }
        }
        }
        else {
        echo "Blah..blah..blah";
        }
?>

 

And got it doing what i intended to do in the first place. 

Yes, i can post the table structure, it is as follows :

 

Table Name : ppricing

Field

Type

id

int(11)

ptitle

varchar(100)

psprice

varchar(100)

peprice

varchar(100)

psummary

mediumtext

ppic

varchar(100)

 

Table Name : pdetails

Field

Type

id

int(11)

pktitle

varchar(100)

listitem

varchar(100)

 

The value in ppricing->ptitle and pdetails->pktitle will be the same.

 

Should i use inner.join?

[redarrow]

im sure this will work dont no tell me cheers.

 

or someone tell me if it a valid mysql select statement.

<?php
$data1 = mysql_query("SELECT ppricing.ptitle,ppricing.newd, pdetails.pktitle,
pdetails.newd FROM ppricing,pdetails WHERE ppricing.ptitle = '$newd'
AND pdetails.pktitle='$newd'") or die(mysql_error());
  $num1=mysql_numrows($data1);
?>

 

or is this correct matching the id in the both columns.

<?php
$data1 = mysql_query("SELECT ppricing.ptitle,ppricing.newd, pdetails.pktitle,
pdetails.newd FROM ppricing,pdetails WHERE ppricing.newd = 'pdetails.newd") or die(mysql_error());
  $num1=mysql_numrows($data1);
?>

 

[MP145]

Thanks redarrow for the reply 

 

What i wanted to accomplish here is :

 

to display the data from the table ppricing which is all in a single row for the variable $newd .  passed on in the url

 

blahblah.com/blah.php?package=blah

 

<?php
$id = $_GET['package'];
$newd=base64_decode($id);
?>

 

but the data in the table pdetails has multiple rows, so when i use this mysql select statement

 

<?php
$data = mysql_query("SELECT * FROM ppricing,pdetails WHERE ppricing.ptitle = '$newd' AND pdetails.pktitle = '$newd'") or die(mysql_error());
        $num=mysql_numrows($data);

        if ($num==0) {
        echo "Check Back Soon";
        } else {
        while($prinfo = mysql_fetch_array( $data ))
        {
         // Data from table ppricing
         // Data from table pdetails
        // Continue data from table ppricing
        }
}
?>

 

It loops the data in the ppricing table which is supposed to be single row.

 

I need it to display the data from table ppricing then loop the data in pdetails and continue displaying the data in ppricing

 

vbnullchar suggested to use inner.join, im not sure how to use that?

 

On the other hand, im kinda thinking of letting it be, as now i got it working by splitting the queries. 

 

Do advice otherwise.