PHP Add quote to array from fetch array

Question

I get error : "Unknown column 'Array' in 'where clause'" perharps from variable $query in my code.

This is my code :

$zzz = mysql_query("SELECT alias FROM table WHERE ColumnA = 'yes'");
while($aaa = mysql_fetch_array($zzz)){
    $array[] = $aaa['alias'];
}
$query = mysql_query("SELECT * FROM table2 WHERE alias NOT IN ($array) ORDER BY Column1 DESC, Column2 DESC");

I want to make a SELECT query WHERE 'alias' in table2 not equal to any data in $array which come from fetch array $aaa.

I got a clue to make an array from fetch array from : Array in SQL Query?

But, i don't know how to add 'quote' for each data in array that made from $aaa.

Could anyone tell me how to do this? :)

Wouter J · Accepted Answer · 2013-03-02 09:32:48Z

2

Why not use nested queries? Example:

$query = mysql_query("SELECT * FROM table2 WHERE alias NOT IN (SELECT alias FROM table WHERE ColumnA = 'yes') ORDER BY Column1 DESC, Column2 DESC");

As noted in my below comment, however, your interaction appears to be vulnerable to injection attacks. This can be avoided to some degree, as others have stated, but as I have also stated, one of the better ways is to use PDO. Example:

try {
    $dbh = new PDO("mysql:host=localhost;dbname=dbname", "user", "password");
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $dbh->prepare("SELECT * FROM table2 WHERE alias NOT IN (SELECT alias FROM table WHERE ColumnA = :bool) ORDER BY Column1 DESC, Column2 DESC");
    $stmt->bindValue(":bool","yes");
    $stmt->execute();
} catch (\PDOException $e) {
    // Something went wrong
}

while ($row = $stmt->fetch()) {
    // do stuff with query
}

PDO ships with php 5.1.

edited Mar 2, 2013 at 9:32

Wouter J

42k15 gold badges113 silver badges114 bronze badges

answered Mar 2, 2013 at 9:04

Daedalus

7,7125 gold badges40 silver badges64 bronze badges

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

13 Comments

Daedalus Over a year ago

However, as a note, your current method of db interaction appears to be vulnerable to injection attacks. I would suggest you look up PDO.

Your Common Sense Over a year ago

DO NOT wrap PDO code into try..catch. it's just useless and wrong

Daedalus Over a year ago

@YourCommonSense Could you explain? Even the php site recommends this for error catching..

Daedalus Over a year ago

@YourCommonSense I've removed the offending code, could you please remove your downvote?

Daedalus Over a year ago

@WouterJ I removed the try-catch as apparently 'it's wrong'. I had it in there before, and I got downvoted for it.

|

mgibsonbr · Accepted Answer · 2013-03-02 09:07:56Z

0

You're trying to use $array directly, and it does not print itself the way you need to. Following the advice in the linked question, you could use implode:

$newarray = implode(", ", $array);
$query = mysql_query("SELECT * FROM table2 WHERE alias NOT IN ($newarray) ORDER BY Column1 DESC, Column2 DESC");

As for adding quotes, you can just concatenate them together. However, I'd also escape the values before quoting, to avoid SQL injection vulnerabilities:

while($aaa = mysql_fetch_array($ambilLarikAkunTerlindungi)){
    $array[] = "'" . mysqli_real_escape_string($aaa['alias']) . "'";
}

edited Mar 2, 2013 at 9:07

answered Mar 2, 2013 at 9:02

mgibsonbr

22.1k7 gold badges74 silver badges115 bronze badges

Collectives™ on Stack Overflow

PHP Add quote to array from fetch array

2 Answers 2

13 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related