How can I use implode to query the database? [duplicate]

Question

I built an array of strings and I'm attempting to query mySQL table. I want to find all rows that match my list of items separated with the word 'AND'. I've tried the code below but got no result back. How can I improve my code to perform the proper query using implode?

Thanks for any help.

 $conditions = array(
     'Monday',
     'Tuesday',
     'Wednesday'
 );
 $connection = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);

 if (mysqli_connect_errno())
{
die("Database connection failed: " . mysqli_connect_error() . " (" . mysqli_connect_errno() . ")");
}

 $query = "SELECT * FROM match_tracker ";

 // append the conditions

 $query.= "WHERE " . implode(" AND ", $conditions);
 $result = mysqli_query($connection, $query);

 if ($result)
{
echo "Results positive.  ";
}

 if (!$result)
{
echo "Empty results.";
die("database query failed.");

Going by your code, your SQL query should look like this: "SELECT * FROM match_tracker WHERE Monday AND Tuesday AND Wednesday". Is that your intended sql string? If you wish to have something more meaningful, you'd have to go by way of associative arrays. — NaijaProgrammer
– NaijaProgrammer, Commented Mar 25, 2015 at 20:44
well your creating an invalid query, but the method should be changed to use IN() — user557846
– user557846, Commented Mar 25, 2015 at 20:46
Additionally you'd have to quote each value in the implode() in order for the query to work. — Jay Blanchard
– Jay Blanchard, Commented Mar 25, 2015 at 20:47
Can you share a sample of your data? I am getting the feeling that @Dagon is more likely right where you're concerned. And please add some error checking to your MySQLi, there are things happening here that you're not aware of. There is no way that a single column value could be 'Monday' AND 'Tuesday' AND 'Wednesday' unless there is temporal misalignment I'm not aware of. — Jay Blanchard
– Jay Blanchard, Commented Mar 25, 2015 at 21:10

Jay Blanchard · Accepted Answer · 2015-03-25 21:45:16Z

3

You need to make sure the values in the implode() are quoted correctly:

$items = "day = '" . implode("' AND day = '", $conditions) . "'"; // day = 'Monday' AND day = 'Tuesday' AND day = 'Wednesday'
$query.= "WHERE " . $items;

The one thing you have forgotten above is a column to test against, such as the day column I have added in the WHERE condition.

If you're wanting to get data from the table where something occurred on a Monday, Tuesday OR Wednesday then you would do this:

$days = "('" . implode("', '", $conditions) . "')"; // ('Monday', 'Tuesday', 'Wednesday')
$query .= "WHERE day IN" . $days;

edited Mar 25, 2015 at 21:45

answered Mar 25, 2015 at 20:49

Jay Blanchard

34.5k17 gold badges82 silver badges130 bronze badges

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

Comments

NaijaProgrammer · Accepted Answer · 2015-03-25 20:54:21Z

0

Going by your code, your SQL query should look like this: "SELECT * FROM match_tracker WHERE Monday AND Tuesday AND Wednesday". Is that your intended sql string? If you wish to have something more meaningful, you'd have to go by way of associative arrays. Something like below would be more appropriate:

$conditions = array(
 'Monday' => '1',
 'Tuesday' => '4', 
 'Wednesday' => '3'
 );
 $connection = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);

if (mysqli_connect_errno())
{
   die("Database connection failed: " . mysqli_connect_error() . " (" . mysqli_connect_errno() . ")");
}

$query = "SELECT * FROM match_tracker WHERE true";

// append the conditions

foreach($conditions AS $key => $value)
{
   $query .= "AND $key = '". mysqli_real_escape_string($connection, $value). "'";
}
$result = mysqli_query($connection, $query);

if ($result)
{
   echo "Results positive.  ";
}

else if (!$result)
{
   echo "Empty results.";
   die("database query failed.");
}

I hope this helps.

answered Mar 25, 2015 at 20:54

NaijaProgrammer

2,9202 gold badges27 silver badges33 bronze badges

2 Comments

NaijaProgrammer Over a year ago

My values are quoted. And although hardcoded, I have also escaped them, just incase he needs to use the same code with data coming from user input

CloudyKooper Over a year ago

Both methods work but I like the associative array more. I can do more with it. Thanks for helping guys!

Collectives™ on Stack Overflow

How can I use implode to query the database? [duplicate]

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related