...

View Full Version : Query: Select * From table Where element In $stringarray



BC3
08-16-2011, 04:36 AM
I have been trying to select all the rows from a table that have an element that matches any of the strings in an array variable. As of right now I have the following code:


$tracker = implode(',',$temp);
echo "<h2>$tracker</h2>";
$query2 = "SELECT * FROM songs WHERE author IN (" . $tracker . ")";

$temp is an array of the strings of interest. The 2nd line I have included to confirm that the elements in the array are correct. The elements have single quotes around them, i.e. 'string1','string2','string3'. I have tried doing this a couple different ways but the majority of them display a query error. However, the above code simply times out when calling the page. The array only has 2 elements in it as of now and the table it queries only has 4 rows with 6 elements each. Does anyone have any guidance/suggestions? Thanks.

bullant
08-16-2011, 04:43 AM
Looks like you need to do some Basic Debugging 101 (http://www.codingforums.com/showpost.php?p=1082858&postcount=1)

Echo out the actual query being run.



$query2 = "SELECT * FROM songs WHERE author IN (" . $tracker . ")";
echo $query2; die();

If you can't see any obvious errors, run the output in an sql window like phpMyAdmin or better still SQLyog (http://www.webyog.com/en/) .

If it works ok in the sql window then there is a problem elsewhere in the code you haven't posted.

BC3
08-16-2011, 04:49 PM
$tracker = implode(',',$temp);
echo "<h2>$tracker</h2>";
$query2 = "SELECT * FROM songs WHERE author IN (" . $tracker . ")";
echo $query2;
$result1= mysql_query($query2);
echo "<p>$result1[artist]</p>"; die();

From the above block of code, I copy and pasted the echoed query ($query2) and put it in PHPMysqlAdmin sql window and it returned the desired results. But $result1 does not seem to contain anything when the code is run. ('artist' is one of the fields in the table) When I remove the last line of the above code and replace it with:


$numrows2 = mysql_num_rows($result1);
echo $numrows2; die()

..it returns 0. I don't understand why the same query works in the mysqladmin but returns nothing from the php file. Thanks again for your help.

guelphdad
08-16-2011, 09:26 PM
echo out $query2 and post it here so we can look at exactly what the query is looking at.

BC3
08-16-2011, 09:39 PM
echo $query2 produces the following:

SELECT * FROM songs WHERE author IN ('beatMaster1','CamCam4')

Old Pedant
08-16-2011, 09:56 PM
I don't see how you can get from this

$tracker = implode(',',$temp);
$query2 = "SELECT * FROM songs WHERE author IN (" . $tracker . ")";
echo $query2;

to this


SELECT * FROM songs WHERE author IN ('beatMaster1','CamCam4')

unless $temp (before the implode, in other words) contained an array with the elements


$temp[0] == 'beatMaster1'
$temp[1] == 'CamCam4'

Is that really the case??? Or did it actually have the elements


$temp[0] == beatMaster1
$temp[1] == CamCam4

??

In other words, I'm asking how the apostrophes got in there.

*WITH* the apostrophes, the query should work. Well, it should work assuming there are any records in songs where the author field matches one of those values.

**************************

Anyway, I'm not a PHP person, at all. But surely this line is not even close to correct:


echo "<p>$result1[artist]</p>";

Surely you need to do something like this, instead:


$row = mysql_fetch_assoc($result1);
echo "<p>" . $row["artist"] . "</p>";

(And that assumes that indeed there is a field in the songs table named "artist". I would assume there is, but of course the SELECT was based on the field author.)

No? All you PHP experts??



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum