[RESOLVED] Compress lots of querys into one...

ElectricRain

Ok now I realise I could solve this by restructuring my table.. but thats going to cause untidyness elsewhere so I dont really wanna. What Im trying to achieve is a print out of the number of unvalidated images, and then a list of them (so Im going to haveto be able to use mysql_fetch_assoc or something similar on this)
If I simply do "SELECT * from pictures where (image1 !='' AND image1ver ='0') OR (image2 != '' AND image2ver = '0')" etc. I get an accurate print out of the number of ROWS but that is not what I want, since each row may contain more than one unvalidated image. Ideas?

//see if there are any images awaiting validation
$searchvalidate1 = mysql_query("SELECT * from pictures where image1 != '' AND image1ver = '0'") or die(mysql_error());
$numval1 = mysql_num_rows($searchvalidate1);
$searchvalidate2 = mysql_query("SELECT * from pictures where image2 != '' AND image2ver = '0'") or die(mysql_error());
$numval2 = mysql_num_rows($searchvalidate2);
$searchvalidate3 = mysql_query("SELECT * from pictures where image3 != '' AND image3ver = '0'") or die(mysql_error());
$numval3 = mysql_num_rows($searchvalidate3);
$searchvalidate4 = mysql_query("SELECT * from pictures where image4 != '' AND image4ver = '0'") or die(mysql_error());
$numval4 = mysql_num_rows($searchvalidate4);
$searchvalidate5 = mysql_query("SELECT * from pictures where image5 != '' AND image5ver = '0'") or die(mysql_error());
$numval5 = mysql_num_rows($searchvalidate5);
$searchvalidate6 = mysql_query("SELECT * from pictures where image6 != '' AND image6ver = '0'") or die(mysql_error());
$numval6 = mysql_num_rows($searchvalidate6);
$numval = $numval1 + $numval2 + $numval3 + $numval4 + $numval5 + $numval6;

SargeZT

To perform multiple queries with the same query, you must put a ; at the end of every line before the first one. And, combining strings is not done with the +, but the .

//see if there are any images awaiting validation
$searchvalidate1 = mysql_query("SELECT * from pictures where image1 != '' AND image1ver = '0';") or die(mysql_error());
$numval1 = mysql_num_rows($searchvalidate1);
$searchvalidate2 = mysql_query("SELECT * from pictures where image2 != '' AND image2ver = '0';") or die(mysql_error());
$numval2 = mysql_num_rows($searchvalidate2);
$searchvalidate3 = mysql_query("SELECT * from pictures where image3 != '' AND image3ver = '0';") or die(mysql_error());
$numval3 = mysql_num_rows($searchvalidate3);
$searchvalidate4 = mysql_query("SELECT * from pictures where image4 != '' AND image4ver = '0';") or die(mysql_error());
$numval4 = mysql_num_rows($searchvalidate4);
$searchvalidate5 = mysql_query("SELECT * from pictures where image5 != '' AND image5ver = '0';") or die(mysql_error());
$numval5 = mysql_num_rows($searchvalidate5);
$searchvalidate6 = mysql_query("SELECT * from pictures where image6 != '' AND image6ver = '0'") or die(mysql_error());
$numval6 = mysql_num_rows($searchvalidate6);
$numval = $numval1.$numval2.$numval3.$numval4.$numval5.$numval6;

In addition to this, you must use mysqli_multi_query to run the query, instead of a normal mysqli_query.

ElectricRain

Thanks 🙂

Weedpacket

Or you could compress lots of queries into one:

$search = mysql_query("Select Count(*)
	from pictures
	where (image1!='' and image1ver='0')
	or (image2!='' and image2ver='0')
	or (image3!='' and image3ver='0')
	or (image4!='' and image4ver='0')
	or (image5!='' and image5ver='0')
	or (image6!='' and image6ver='0')");
$numval = mysql_result($search,0);

Next step of course would be to renormalize the database.