[RESOLVED] using php array in mysql select query.

[acemods]

Im working on a price label php application for work which im nearly 100% there with but when i try to select selected stock codes to print out onto address labels using the IN() if i say 2 labels of one code the part number apears twice in the array but the select query will only show it once. can anybody point me in the write direction.

this is just a example part of my script.

$part = array('AC0001','AC0002','AC0001','AC0066');

$sql = "SELECT * FROM barcode WHERE part IN('".implode("', '", $part)."')";
$result = mysql_query($sql); 

the result will only give me labels for AC0001 AC0002 and AC0066 where i want the result to be AC0001 AC0002 AC0001 and AC0066 how do i make mysql display all results in the php array.

Acemods

[HalfaBee]

Have a look at this thread http://phpbuilder.com/board/showthread.php?t=10356034

[acemods]

Hi HalfaBee, i have had a look at the thread but i dont think that would help me as the part array in my final version of the code is dynamic and could be 200 items.

[leatherback]

It will take a bit more effort, but..

In your 'while loop' you create the label, and store it in an array, where the key is your item ID
When all records have been retrieved, you loop though the original array with inut ID's and print out a label for each ID that you encounter.

OR you loop through your input array, and you count for each ID the number of times you need a label. Then, when retrieving the records, you just use the same records set as often as you need it.

[acemods]

thanks leatherback, can you point me in the right direction for sample code.

[HalfaBee]

$sql = "SELECT * FROM barcode";
$result = mysql_query($sql); 
while( $bc = mysql_fetch_assoc( $result ) ) {
    $barcodes[$bc['part']] = $bc;
}
$part = array('AC0001','AC0002','AC0001','AC0066'); 
foreach( $part as $bc ) {
    print_barcode( $barcodes[$bc] );
}

[bradgrafelman]

As a minor optimization, I would suggest that you keep the original query (including the IN (...) clause) but take the while() statement from HalfaBee's code snippet.

Essentially, you copy the rows that were found in the SQL table into an array. Then, you could loop through your array of labels in the $part variable and, if found in the array of SQL rows, output each barcode.

[HalfaBee]

I wasn't sure that having 200 products in the query would have been any better than reading the whole DB.

You could probably cache the result and store it in a file, rather than doing the query every time.

[acemods]

thats for all the help HalfaBee & bradgrafelman i am trying your new code just now but all im getting is array its looping the correct number of times but its not pulling the information from the array.

also how do i go about if i want to display other fields in the same array.

this is the script i have so far

<?php
// Make a MySQL Connection
include("database.inc");
$part = array('AC0001','AC0002','AC0001','AC0066');

$conn = mysql_connect("$host", "$user", "$passwd");
mysql_select_db("$database", $conn);

$sql = "SELECT * FROM barcode WHERE part IN('".implode("', '", $part)."')";
$result = mysql_query($sql);
while( $bc = mysql_fetch_assoc( $result ) ) {
    $barcodes[$bc['part']] = $bc;
}
$part = array('AC0001','AC0002','AC0001','AC0066');
foreach( $part as $bc ) {
?>
<table>
<tr>
<TD><?php echo $barcodes[$bc]; ?></TD>
</tr>
</table>
<?php
}

print_r($barcodes);
echo "";
var_dump($barcodes);

?>

Thanks

Acemods

[leatherback]

In the code that you are using, $barcodes[$bc] will be the equivalent og $bc in the while( $bc = mysql_fetch_assoc()) loop. Hence, you can refer to the individual elements by using:

<?php echo $barcodes[$bc]['columname']; ?>

Check this by placing a print_r(barcodes[$bc]); in the foreach loop, and you'll see for yourself 😃

Cheers,

Jelle

[bradgrafelman]

Thus, if there's only one column from the database that you want to store for each row, change the $bc in that line to reference only that column (e.g. $bc['UPC']).

[acemods]

many thanks for everybodys help me get this little web app for my work up and running its looking really good. if anybody is looking for the same type of app and is wanting the code. just PM me.

Acemods

[y88admin]

Going back to Acemods original expression. I was wondering If I could afford a mild deviation and ask:-

Is it possible to combine the 'LIKE' expression in the WHERE clause alongside 'IN' ?