Array returns nothing from mysql if more than one value selected

Question

How do I get all the results of my query to show? If my $author prints as Array ( [0] => 1 ) the query pulls correct data. However, if my $author prints as Array ( [0] => 1 [1] => 8 ) or any combination of values received from my form, then it returns empty.

The array prints correctly, so I am moving from that point forward and assuming the problem is the query but cannot figure it out.

$query = "SELECT * FROM estudos";
if (isset($author)){
    $query.=" WHERE author='" . implode ($author) ."'";
    print_r ($author);
}

Here is my php and html...

$results=$dbclient->query($query) or die(mysqli_error());

<?php 
while($row=mysqli_fetch_array($results))
    echo"<tr><td class='name'>$row[name]</td>";
?>

WHERE author='" . implode ($author) ."'"; WTF?!? Have you ever come across SQL's IN — Mark Baker
– Mark Baker, Commented Nov 6, 2013 at 19:16
@MarkBaker Actually, I am VERY new at this, so I must say, no I have not. Any suggestions would be appreciated. I will check it out though. — Jeremy Tyler
– Jeremy Tyler, Commented Nov 6, 2013 at 19:18

Amal · Accepted Answer · 2013-11-06 19:33:11Z

2

You're missing the glue parameter for implode(). Without that, your $query would look something like this:

SELECT * FROM estudos WHERE author='FooBarBaz'

This is a syntactically valid SQL query, but it doesn't do what you want.

You're probably looking for the IN clause.

$query .= " WHERE author IN (" . implode (',', $author) .")";

Note that I haven't fixed the SQL injection vulnerabilities in your query. I recommend you switch to MySQLi or PDO and start using parameterized queries to be safe from SQL injection.

edited Nov 6, 2013 at 19:33

answered Nov 6, 2013 at 19:16

Amal

76.8k18 gold badges134 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

hek2mgl Over a year ago

hihi! :) implode() again.. however, you might give an example for a prepared statement here...

Jeremy Tyler Over a year ago

I just tried this, but I still only get results for one item in array.

Amal Over a year ago

@JeremyTyler: Can you try to echo $query; before execution and post the output?

Amal Over a year ago

@JeremyTyler: Glad to have been of help :)

viblizn · Accepted Answer · 2013-11-06 19:28:53Z

1

You need to use IN condition in your SQL Query

$query.=" WHERE author IN (" . implode (',',$author) .")";

SQL IN Condition

if author is string your code will be

 if (isset($author)){
$query . = "("
foreach ($author as $value)
{
    $query .= "'".$value."',";
}
$query = substr($query,0,-1);
$query .= ")";

    print_r ($author);
}

edited Nov 6, 2013 at 19:28

answered Nov 6, 2013 at 19:18

viblizn

1324 bronze badges

Comments

abhiklpm · Accepted Answer · 2013-11-06 19:32:29Z

0

probably author names will be string and you should format it it like below

    IN ('author1', 'author2')

A php function which will help you

function quote($str) {
    return sprintf("'%s'", $str);
}
$authors = array('author1', 'author2', 'author3');
WHERE author IN (implode(',', array_map('quote', $authors)));

edited Nov 6, 2013 at 19:32

answered Nov 6, 2013 at 19:23

abhiklpm

1,6532 gold badges16 silver badges22 bronze badges

Collectives™ on Stack Overflow

Array returns nothing from mysql if more than one value selected

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related