Strange doubled query and blank field

[WarKirby]

I'm writing a script to add some columns to an existing table, and also make a few more, with one being populated by data extracted from the first.

 

This seems to work fine on my test code, but when I attempt it on my live production database under the same conditions, it's failing for reasons  I cannot discern.

 

This is the code snippet where the problem lies:

$sales = mysql_query("SELECT * FROM tblVendorSales ORDER BY timeSold") or die(mysql_error());//This resource contains the entireity of tblVendorSales

echo("Selected ".mysql_num_rows($sales)." records from tblVendorSales<br>");

while($row = mysql_fetch_array($sales))
{	
	echo("Starting a row<br>");
	$querystring = "SELECT * FROM tblProducts WHERE articleNumber=".$row[articleNumber];
	echo($querystring);
	$articleCheck = mysql_query($querystring) or die($querystring.$row[productName]."<br>".mysql_error());
	echo("Articlecheck<br>");
	if(($test = mysql_fetch_array($articleCheck)) == FALSE)
	{
		$querystring = "INSERT INTO tblProducts (articleNumber,productName,releaseDate) VALUES "
                ."(".QuoteSmart(trim($row['articleNumber'])).","
			.QuoteSmart(trim($row['productName'])).","
                .QuoteSmart(trim($row['timeSold'])).")";
		echo("A product is new, adding it:".$querystring."<br>");
		mysql_query($querystring) or die(mysql_error());
		echo("finished adding a product<br>");
	}
	else
	{
	}
}
echo("Done Populating tblProducts<br>");

 

I know there are a hell of a lot of echoes there, they're just for debug purposes. Their output is:

 

Selected 9510 records from tblVendorSales
Starting a row
SELECT * FROM tblProducts WHERE articleNumber=101005002Articlecheck
A product is new, adding it:INSERT INTO tblProducts (articleNumber,productName,releaseDate) VALUES (101005002,'Energy Wave','0')
finished adding a product
Starting a row
SELECT * FROM tblProducts WHERE articleNumber=SELECT * FROM tblProducts WHERE articleNumber=
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

 

For reasons I cannot discern, despite there being 9510 records in the table, it somehow comes up with nothing on the second one.

Having a look over it again though, I noticed this:

SELECT * FROM tblProducts WHERE articleNumber=SELECT * FROM tblProducts WHERE articleNumber=

 

It seems I've somehow doubled a query in there. But where? I'm quite confused now. Or maybe it's just because I forgot to add <br> in some of them.

 

Can anyone tell me where I've gone wrong here?

[jl5501]

your error messages are clearly showing you have no id in your query

 

try changing

 

$querystring = "SELECT * FROM tblProducts WHERE articleNumber=".$row[articleNumber];

 

to

 

$querystring = "SELECT * FROM tblProducts WHERE articleNumber=".$row['articleNumber'];

[PFMaBiSmAd]

Your first query - "SELECT * FROM tblVendorSales ORDER BY timeSold" either contains a NULL or an empty string in the articleNumber column. Have you examined your data in your table to see what is occurring?

 

If you intentionally have NULL/empty data, you would need to change the first query so that it does not return rows with NULL/empty values. If you don't intentionally have NULL/empty data values, you would need to determine how they are occurring and eliminate the problem that is allowing NULL/empty values to be inserted in the first place (and remove existing rows that have NULL/empty values now.)

[WarKirby]

Your first query - "SELECT * FROM tblVendorSales ORDER BY timeSold" either contains a NULL or an empty string in the articleNumber column. Have you examined your data in your table to see what is occurring?

 

If you intentionally have NULL/empty data, you would need to change the first query so that it does not return rows with NULL/empty values. If you don't intentionally have NULL/empty data values, you would need to determine how they are occurring and eliminate the problem that is allowing NULL/empty values to be inserted in the first place (and remove existing rows that have NULL/empty values now.)

 

There's an issue. Trying to check the data earlier, phpmyadmin wasn't working. I contacted the host about it and they've sorted it now, so maybe that was the problem. there are no entries that have a null value for articlenumber, so at this point the only possibility I can see is maybe a databse hiccup or somesuch.

 

Will test again and report back within a day or so.

[WarKirby]

Yep, it mysteriously started working with no changes on my part. Must've been a host problem. Marking solved.

 

I wonder why this thread was moved, it seemed like the mysql forum was exactly the right place for it.