Searching for Id in MySql from an array using For Loop

Question

I have an array of tag names which I passed via POST Method and I these tag names has its corresponding tag_id in the database. All I want is to search the id while iterating the array of tag names and I want the result of each query to be stored in an empty array. I think I just misunderstood something or what.

$tags_array = ['shoes','gadgets','fashion','food'];
$tags_array_id = [];
$tags_sql = '';

foreach ($tags_array as &$tag) {
    $sql = "SELECT tag_id FROM `tbltag` WHERE tag_name = ".$tag." group by tag_name";
    $query = mysqli_query($conn, $sql);
    $result = mysqli_fetch_row($query);
    $tags_array_id[] = $result[0];
};

Please help guys, suggestions highly appreciated.

My suggestion is to run a single query, using the sql keyword in and loop through the results. — Dan Bracuk
– Dan Bracuk, Commented Feb 10, 2016 at 18:24
I don't see the $_POST data you are using there. You might want to review PHP's implode function. Also, you should probably use one query. Look at MySQL's IN() function. — jjwdesign
– jjwdesign, Commented Feb 10, 2016 at 18:26
your query doesn't run because $tag isn't quoted. if $tags_array comes from user input you're vulnerable to sql injection — FuzzyTree
– FuzzyTree, Commented Feb 10, 2016 at 18:27
I just make the $tags_array static on the question material because its fetched from db. Many to mention. But thanks by the way (Y) — vistajess
– vistajess, Commented Feb 10, 2016 at 18:28

jjwdesign · Accepted Answer · 2016-02-10 18:59:11Z

2

Basically something like:

$sql = "SELECT * FROM `tbltag` WHERE tag_name IN('".implode("','", $tags_array)."')";

One last comment: You probably want to search for tag_id's instead of tag names, as you'll probably have those in your input ($_POST/$_GET). And as FuzzyTree has mentioned above, you'll want some type of ID validation and some kind of escaping of data going into the query to avoid SQL injection.

edited Feb 10, 2016 at 18:59

answered Feb 10, 2016 at 18:27

jjwdesign

3,3229 gold badges43 silver badges67 bronze badges

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

Comments

budirec · Accepted Answer · 2016-02-10 18:28:58Z

1

Use this instead

$tags_array = ['shoes','gadgets','fashion','food'];
$tags = implode("','", $tags_array);
$tags = "'".$tags."'";
$sql = "SELECT tag_id FROM tbltag WHERE tag_name IN ({$tags})";

answered Feb 10, 2016 at 18:28

budirec

2881 silver badge5 bronze badges

Comments

Matt · Accepted Answer · 2016-02-10 18:57:35Z

0

Question: Do you need the & in your foreach loop? You are not directly modifying the tags_array just using the information?

Trying adding the number to the array e.g add $x so you are adding in 1 record into 1 part of the array as you loop though.

$tags_array = ['shoes','gadgets','fashion','food'];
$tags_array_id = [];
$tags_sql = '';
$x = 0;

foreach ($tags_array as &$tag) {
    $sql = "SELECT tag_id FROM `tbltag` WHERE tag_name = ".$tag." group by tag_name";
    $query = mysqli_query($conn, $sql);
    $result = mysqli_fetch_row($query);
    $tags_array_id[$x] = $result[0];
    $x++;
};

edited Feb 10, 2016 at 18:57

answered Feb 10, 2016 at 18:33

Matt

1,7573 gold badges13 silver badges26 bronze badges

Collectives™ on Stack Overflow

Searching for Id in MySql from an array using For Loop

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related