using an array within a mysql query

[pushyr]

is it possible to query mysql with an array?

i've googled and came up with this, which says you have to put the array in curly brackets, but it doesn't work...

$page_titles_y['titles'] is my array

SELECT *
FROM table
WHERE page_title = '{$page_titles_y['titles']}' ";

 

[grtammi]

I've used this syntax before; unfortunately, I stopped because the eyes are getting old, and the editor I'm using doesn't syntax-colour embedded PHP.

Make sure your SQL statement is enclosed in double-quotes if you're going to use the {} notation.

$sql = "SELECT * FROM table WHERE page_title = '{$page_titles_y['titles']}'";

Alternatively, this works also:

$sql = "SELECT * FROM table WHERE page_title = '" . $page_titles_y['titles'] . "'";

I prefer the latter method; it's the old eyes, and I personally think it's easier to pick the variable out of the code using the 2nd method.



Greg

 

[grtammi]

And if I had read the whole post ... I would have added this:

You can target elements of an array and embed into a SQL statement ... however, if $page_titles_y['titles'] is in itself an array, then you need to somehow concatenate all the elements of $page_titles_y['titles'] into one variable.

$sql = "SELECT * FROM table WHERE page_title IN ('" . implode(",", $page_titles_y['titles']) . "')";

Greg

 

[pushyr]

hey greg, i tried all your examples but none worked??

thinking about it i prefer not to use the implode example (even though i tried it), as i would expect some page titles could already have commas within their text, thus would throw it off a little

any more suggestions.. or do you think i've done something wrong at my end?

 

[vacunita]

Greg's second example is certain to work. Perhaps you can tell us how it doesn't work? do you get an an error?

Is there actually something in the 'titles' of your array there? if you just echo out the variable is there something in there?

echo $page_titles_y['titles'];

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[pushyr]

i'm not getting any errors... here's my code as per gregs second example...

$sql = "SELECT * 
FROM 09_websites 
WHERE supplier_id = '$supplier_id'
AND site_url = '$stat_site_url'
AND page_title = '" . $page_titles_y['titles'] . "'";

if i echo out the 'titles' bit of my array inside the square brackets i get nothing (if thats what you meant?)

but i'd converted my original array to this after reading the google example as first mentioned..

$page_titles_y['titles'] = $duplicate_page_titles;

if i preview either array... $page_titles_y['titles'] or array... $duplicate_page_titles, i get the following...

Array
(
    [0] => Advertising Agency | London | UK - Internet Advertising and Traditional Campaigns
    [1] => Ecommerce Solutions
)

 

[vacunita]

We seem to have a discrepancy here.

So just to clear some things up. if you do exactly as follows what do you get:

echo $page_titles_y['titles'];

Just copy that, and tell what output you get form that line.



As it stands you are referencing the titles key in your array, but your output:
Array
(
[0] => Advertising Agency | London | UK - Internet Advertising and Traditional Campaigns
[1] => Ecommerce Solutions
)

shows your array to have numeric keys. 0 and 1.

This means either your array key titles is in itself another array, or you just aren't referencing the correct key in your array.






----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[pushyr]

echo $page_titles_y['titles']; ...gives me the following,

Array

 

[grtammi]

Sounds like if the array is populated, you'll have to use the implode method. I'm not sure how the commas will affect the page_title IN '( ... clause of the SQL statement though.

Greg

 

[vacunita]

Si it is in fact an array in itself.
Since its an array, you need to address it as such also, by specifying a key.
So more likely what you want to do is:

$sql = "SELECT * FROM table WHERE page_title = '" . $page_titles_y['titles'][red][0][/red] . "'";

This will let you address the 0 key from your titles array.

[red][0][/red] => Advertising Agency | London | UK - Internet Advertising and Traditional Campaigns






----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[pushyr]

ok, this works... but as you point out it only addresses the 0 key

how do i address other numbered keys in the array

 

[pushyr]

i should mention that i'm using a while loop to echo the results.. so this is why i ask how i address the other keys

 

[vacunita]

You need to either add more conditional statements to your query, and change the key in each one, or do as Greg suggested and implode the inner array, into a comma separated string.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[pushyr]

how do i explore the conditional statements? i definitely cant use the comma separated string example as i know i'll run into trouble when a comma appears within a page title text

 

[vacunita]

$sql = "SELECT * FROM table WHERE page_title = '" . $page_titles_y['titles'][0] . "' [red]OR page_title = '" . $page_titles_y['titles'][[blue]1[/blue]] . "' OR page_title = '" . $page_titles_y['titles'][[blue]2[/blue]] . "' [/red]";

etc...

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[pushyr]

ok i see your point... is there a different way to do this... i mean could i convert the array to how it should be to make it work?

 

[vacunita]

Not sure what you mean.

The array is correct, and those would be the ways to use it.

As long as you mind the quotes, the commas inside the article names should not be a problem if using implode

$sql = "SELECT * FROM table WHERE page_title [red]IN('" [/red] . implode("[highlight]','[/highlight]", $page_titles_y['titles']) .[red]"')[/red]";
[code]


----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

I'm not sure how the commas will affect the page_title IN '( ... clause of the SQL statement though.
[/quote[

answer: not at all. of more concern is the escaping of embedded single quotes etc.

and i think the IN method is still best, as both you and vacunita originally pointed out.

so something like this should work

$sql = "	SELECT 
			* 
			FROM 
				table 
			WHERE 
				page_title IN ('" . implode ("','",array_walk($page_titles_y['titles'], 'mysql_real_escape_string')) . "')";

 

[pushyr]

ok... im using the implode solution

anyway i feel confident that of exhausted all options with all of you guys and you all agree on using implode, which works by the way

i'll try it out with commas in the page titles to see what happens

jpadie... i haven't tried your solution using mysql_real_escape_string but i'll try it out and will get back to confirm my results