Selecting data from msql database blank fields getting filled in by previous result

robrayner · January 31, 2013

Hi All

I have got a little issue with a bit of php code. I am trying to fill in a table with content pulled from a mysql database. The data is getting returned OK but if a field is blank then the result from the previous result is getting fill into the table making it look like there is duplicates.

See below my code

$sql = "SELECT `SERIALNO`,`FRTYPE`, `RMA`, `COMMENTS`, `ADVICENOTE` FROM `products` WHERE `ADVICENOTE` = '$ADVICENOTE' AND `SHIPDATE` = '$SHIPDATE' AND `COMPANY_id` = '$COMPANY_id'

ORDER BY `SERIALNO` ASC LIMIT 0 , 3000";

$query = mysql_query($sql);

$i = '1';

while ($row = mysql_fetch_array($query))

{

$SERIALNO = $row["SERIALNO"];

$FRTYPE = $row["FRTYPE"];

$RMA = $row["RMA"];

$COMMENTS = $row["COMMENTS"];

$q = $i++;

$sql1 = "SELECT * FROM `sims` WHERE `SERIALNO` = '$SERIALNO'";

$query1 = mysql_query($sql1);

while ($row1 = mysql_fetch_array($query1))

{

$SIMNUMBER = $row1["SIMNUMBER"];

$DATANUMBER = $row1["DATANUMBER"];

$NETWORK = $row1["NETWORK"];

$SMSNUMBER = $row1["SMSNUMBER"];

}

echo "<table width =\"100%\"><tr>

<td align=\"center\" Class=\"TableRowTitle\" width=\"5%\">Serial No</td>

<td align=\"center\" Class=\"TableRowTitle\" width=\"25%\">Sim Number</td>

<td align=\"center\" Class=\"TableRowTitle\" width=\"10%\">Data Number</td>

<td align=\"center\" Class=\"TableRowTitle\" width=\"10%\">Voice Number</td>

<td align=\"center\" Class=\"TableRowTitle\" width=\"20%\">Network</td>

<td align=\"center\" Class=\"TableRowTitle\" width=\"15%\">Comments</td>

<td align=\"center\" Class=\"TableRowTitle\" width=\"5%\">RMA</td></tr>";

echo "<tr><td><a href=\"checkserialnew.php?row_ID=$SERIALNO\">$SERIALNO</a></td>

<td nowrap>$SHIPDATE</td>

<td nowrap>$FRTYPE</td>

<td>";

if ( $SIMNUMBER == "") {echo "NO SIM"; } else { echo "$SIMNUMBER"; }

echo "</td><td>";

if ( $DATANUMBER == "") {echo "NO DATA NUMBER"; } else { echo "$DATANUMBER"; }

echo "</td><td>";

if ( $SMSNUMBER == "") {echo "NO VOICE NUMBER"; } else { echo "$SMSNUMBER"; }

echo "</td><td>$NETWORK</td><td>$COMMENTS</td><td>";

echo "<a href=\"returnwarranty.php?row_ID=$RMA\">$RMA</a><td></tr>" ;

}

echo "</table>";

echo "Shipped to <b>$COMPANY_id</b> on Advice Note Number <b>$ADVICENOTE</b> QTY <b>$q</b><br>";

Thanks

Rob.

Barand · January 31, 2013

Don't run queries inside loops. Use a single query with a JOIN

$sql = "SELECT p.SERIALNO, SHIPDATE, FRTYPE,
   IF (SIMNUMBER='', 'NO SIM', SIMNUMBER) as SIMNUMBER,
   IF (DATANUMBER='', 'NO DATA NUMBER', DATANUMBER) as DATANUMBER,
   IF (SMSNUMBER='', 'NO VOICE NUMBER', SMSNUMBER) as SMSNUMBER,
   NETWORK, COMMENTS, RMA  
FROM products p
   INNER JOIN sims s ON p.SERIALNO = s.SERIALNO
WHERE ADVICENOTE = '$ADVICENOTE'
   AND SHIPDATE = '$SHIPDATE'
   AND COMPANY_id = '$COMPANY_id'
ORDER BY p.SERIALNO
LIMIT 0 , 3000";

$res = mysql_query($sql);

echo "<table width =\"100%\"><tr>
<td align=\"center\" Class=\"TableRowTitle\" width=\"5%\">Serial No</td>
<td align=\"center\" Class=\"TableRowTitle\" width=\"10%\">Ship Date</td>
<td align=\"center\" Class=\"TableRowTitle\" width=\"5%\">FR Type</td>
<td align=\"center\" Class=\"TableRowTitle\" width=\"25%\">Sim Number</td>
<td align=\"center\" Class=\"TableRowTitle\" width=\"10%\">Data Number</td>
<td align=\"center\" Class=\"TableRowTitle\" width=\"10%\">Voice Number</td>
<td align=\"center\" Class=\"TableRowTitle\" width=\"20%\">Network</td>
<td align=\"center\" Class=\"TableRowTitle\" width=\"15%\">Comments</td>
<td align=\"center\" Class=\"TableRowTitle\" width=\"5%\">RMA</td></tr>";

$q=0;
while ($row = mysql_fetch_assoc($res)) {
   $q++;
   // output row
}

echo "</table>";
echo "Shipped to <b>$COMPANY_id</b> on Advice Note Number <b>$ADVICENOTE</b> QTY <b>$q</b><br>";

robrayner · February 1, 2013

Thanks for your reply I have tried what you have suggested but I am getting no results returned and when I use phpmyadmin and run the query I am getting Column 'COMPANY_id' in where clause is ambiguous .

Barand · February 1, 2013

Have you a COMPANY_id in sims table too? You need to specify which table to use. I also removed spaces between IF and (.

$sql = "SELECT p.SERIALNO, SHIPDATE, FRTYPE,
   IF(SIMNUMBER='', 'NO SIM', SIMNUMBER) as SIMNUMBER,
   IF(DATANUMBER='', 'NO DATA NUMBER', DATANUMBER) as DATANUMBER,
   IF(SMSNUMBER='', 'NO VOICE NUMBER', SMSNUMBER) as SMSNUMBER,
   NETWORK, COMMENTS, RMA  
FROM products p
   INNER JOIN sims s ON p.SERIALNO = s.SERIALNO
WHERE ADVICENOTE = '$ADVICENOTE'
   AND SHIPDATE = '$SHIPDATE'
   AND p.COMPANY_id = '$COMPANY_id'
ORDER BY p.SERIALNO
LIMIT 0 , 3000";

robrayner · February 1, 2013

The only thing common between both tables is the SERIALNO

SERIALNO

SHIPDATE

FRTYPE

COMMENTS

RMA

ADVIVENOTE

COMPANY_id

are in the products table and

SIMNUMBER

SMSNUMBER

DATANUMBER

NETWORK

SERIALNO

are in the sims table

Edited February 1, 2013 by robrayner

robrayner · February 7, 2013

Thanks Barand

This is working to an extent it is pulling data back from the database now, but it is now missing out the units which dont have sims in.

This is what it is pull in out of the database.

SERIALNO SHIPDATE FRTYPE SIMNUMBER DATANUMBER SMSNUMBER NETWORK COMMENTS RMA

021211 2013-01-03 FR340 8944100030166309**** 07747948*** 07880244*** Vodafone Replacement for CBX00169 NULL

021212 2013-01-03 FR340 8944100030166309**** 07747948*** 07880244*** Vodafone Replacement for CBX00265 NULL

021213 2013-01-03 FR340 8944100030200787**** 07867579*** 07880454*** Vodafone Replacement for CBX00284 NULL

021214 2013-01-03 FR340 8944100030200787**** 07867579*** 07880454*** Vodafone Replacement for CBX00266 NULL

021215 2013-01-03 FR340 8944100030200787**** 07867579*** 07880454*** Vodafone Replacement for CBX00282 NULL

but should be pulling back

SERIALNO SHIPDATE FRTYPE SIMNUMBER DATANUMBER SMSNUMBER NETWORK COMMENTS RMA

021207 2013-01-03 FR340 Replacement for CBX00115 NULL

021208 2013-01-03 FR340 Replacement for CBX00358 NULL

021209 2013-01-03 FR340 Replacement for CBX00168 NULL

021210 2013-01-03 FR340 Replacement for CBX00299 NULL

021211 2013-01-03 FR340 8944100030166309**** 07747948*** 07880244*** Vodafone Replacement for CBX00169 NULL

021212 2013-01-03 FR340 8944100030166309**** 07747948*** 07880244*** Vodafone Replacement for CBX00265 NULL

021213 2013-01-03 FR340 8944100030200787**** 07867579*** 07880454*** Vodafone Replacement for CBX00284 NULL

021214 2013-01-03 FR340 8944100030200787**** 07867579*** 07880454*** Vodafone Replacement for CBX00266 NULL

021215 2013-01-03 FR340 8944100030200787**** 07867579*** 07880454*** Vodafone Replacement for CBX00282 NULL

thanks

Rob.

Barand · February 7, 2013

Change INNER JOIN to LEFT JOIN

thara · February 7, 2013

@Barand if you have time.. Please explain the difference between inner join and left join, still I confuse to understand it. Phycho also used left join in my previous post http://forums.phpfreaks.com/topic/274066-how-can-i-make-this-mysql-query/

But I dont have any idea why he did it so?

Thank you.

robrayner · February 7, 2013

Thanks Barand that did the job. Like thara has said can you explain the difference between inner joins and left joins

And again thanks for your help.

Barand · February 7, 2013

my tutorial may help

http://www.phpfreaks.com/tutorial/data-joins-unions

Sign In

Selecting data from msql database blank fields getting filled in by previous result

Recommended Posts

robrayner

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

robrayner

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

robrayner

Link to comment

Share on other sites

robrayner

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

thara

Link to comment

Share on other sites

robrayner

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information