Using arrays in queries

[DeepakJ]

How would I make a query like:

 

SELECT * FROM productid WHERE $arrayelements[] are in invoice num.

 

What I want is all the invoicenum in the array to be in the selection.

[hitman6003]

$query = "SELECT * FROM productid WHERE invoice_num IN('" . implode("', '", $arrayelements) . "')"

[DeepakJ]

It doesn't seem to work. This is my code.

 

 

<?php
$hostname = "localhost";
$username = "root";
$password = "98989lol";
$dbname = "licensinginformation";
$customerid = $_GET['user'];
$productid = $_GET['user1'];


mysql_connect($hostname, $username, $password) or DIE("Unable to connect to MySQL server $hostname");
$selected = mysql_select_db($dbname) or DIE("Could not select requested db $dbname");

$querya = "SELECT * FROM invoiceid WHERE customerid='$customerid'";
$aResult = mysql_query($querya);

while($row1=mysql_fetch_array($aResult)){
$invoicenum[]= $row1['invoicenum'];
}

$queryd = "SELECT * FROM productid WHERE invoicenum IN('" . implode("', '", $invoicenum) . "')";
$dResult = mysql_query($queryd) or die(mysql_error());
while($row2=mysql_fetch_array($dResult)){
if ($row2['productid']="")
{
	$tableid = $row2['tableid'];
	$queryc = "UPDATE productid SET productid='$productid' WHERE tableid = '$tableid'";
	mysql_query($queryc) or die(mysql_error());
	break;
}
}
echo "Data successfully entered."

[DeepakJ]

I need help with this... BAD

 

[hitman6003]

You can simplify greatly.

 

Also, edit your post above and remove your connection information.

 

$query = "SELECT invoicenum FROM invoiceid WHERE customerid = '$customerid'";
$result = mysql_query($query) or die(mysql_query());

while($row = mysql_fetch_array($result)){
$invoices[]= $row['invoicenum'];
} 

echo "The following invoices will be updated: " . implode(", ", $invoices);

$query = "UPDATE productid SET productid = " . $productid . " WHERE invoicenum IN(" . implode(", ", $invoices) . ") AND (productid = "" OR productid IS NULL)";
mysql_query($query) or die(mysql_error());

echo mysql_affected_rows() . " rows were updated by the last query<br /><br />" . $query;

[DeepakJ]

It doesnt matter its localhost info. Pass etc will be changed when it is implemented finally on a server.

[DeepakJ]

I'm getting a parse error with the code you gave.

[DeepakJ]

The parse error is in the query line

[hitman6003]

Might be the double quotes inside the implode function..

 

change it to:

 

implode(', ', $invoices)

[DeepakJ]

Its not that its the part at the end. "" OR productid IS NULL)";

[DeepakJ]

I'm not sure how to fix it cause i am new at this sort of thing

[hitman6003]

oops...wrong quotes...

 

change:

 

(productid = "" OR productid IS NULL)

 

to

 

(productid = '' OR productid IS NULL)

[DeepakJ]

Thats not what I want to do though. I want to update the product id on ONE of the invoices once. Doesnt matter which, aslong as it has a null string. There is a preivous script which sets the product ID to null when it is purchased and this interface is for licensing. Invoicenum is irrelavent except for the fact that it connects productid and invoiceid.

[DeepakJ]

This is what I have so far but it doesn't work. Nothing happens. Do you see anything wrong with the loop?

<?php
$hostname = "localhost";
$username = "root";
$password = "98989lol";
$dbname = "licensinginformation";
$customerid = $_GET['user'];
$productid = $_GET['user1'];


mysql_connect($hostname, $username, $password) or DIE("Unable to connect to MySQL server $hostname");
$selected = mysql_select_db($dbname) or DIE("Could not select requested db $dbname");

$querya = "SELECT * FROM invoiceid WHERE customerid='$customerid'";
$aResult = mysql_query($querya);

while($row1=mysql_fetch_array($aResult)){
$invoicenum[]= $row1['invoicenum'];
}

$queryd = "SELECT * FROM invoiceid, productid WHERE invoiceid.invoicenum = productid.invoicenum && customerid= '$customerid'";
//queryd = "SELECT * FROM productid WHERE invoicenum IN('" . implode("', '", $invoicenum) . "')";
$dResult = mysql_query($queryd) or die(mysql_error());
$boolean = false;
while($row2=mysql_fetch_array($dResult)){
if ($row2['productid']="")
{
	$tableid = $row2['tableid'];
	$queryc = "UPDATE productid SET productid='$productid' WHERE tableid = '$tableid'";
	mysql_query($queryc) or die(mysql_error());
	echo "Data successfully entered.";
	$boolean = true;
	break;
}
}
if ($boolean=false){
echo "The customer does not have enough licenses.";
}



?>