Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    BC3
    BC3 is offline
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Query: Select * From table Where element In $stringarray

    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:

    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.

  • #2
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    Looks like you need to do some Basic Debugging 101

    Echo out the actual query being run.

    PHP Code:
    $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 .

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

  • #3
    BC3
    BC3 is offline
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    $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:

    Code:
    $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.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    echo out $query2 and post it here so we can look at exactly what the query is looking at.

  • #5
    BC3
    BC3 is offline
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    echo $query2 produces the following:

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

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    I don't see how you can get from this
    Code:
    $tracker = implode(',',$temp);
    $query2 = "SELECT * FROM songs WHERE author IN (" . $tracker . ")";
    echo $query2;
    to this
    Code:
    SELECT * FROM songs WHERE author IN ('beatMaster1','CamCam4')
    unless $temp (before the implode, in other words) contained an array with the elements
    Code:
    $temp[0] == 'beatMaster1'
    $temp[1] == 'CamCam4'
    Is that really the case??? Or did it actually have the elements
    Code:
    $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:
    Code:
    echo "<p>$result1[artist]</p>";
    Surely you need to do something like this, instead:
    Code:
    $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??


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •