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

[robrayner]

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=\"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>";

 

 

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]

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]

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]

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]

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]

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]

Change INNER JOIN to LEFT JOIN

[thara]

@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]

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]

my tutorial may help

 

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