Topic awaiting preservation: MySQL select statements using an array

Greetings!

I've Googled this, and the couple of results I did find either didn't work or wasn't what I was looking for.

Idea:

$sql = mysql_query("select counties from table where status = 'enabled'");
$results = mysql_fetch_array($sql);

So $results holds an array of my counties. So far, so good.

Now, I want to take that array and do another query for rows that include a field that matches any item in the array.

an english version of my idea of the query:
select * from table2 where county is in the array called $results

Not sure what that's called, so I'm having a hard time finding info.

Or, should I be just constructing the first query differently to accomplish this all at once?

[uber_quick_reply] In general in that case I do something like :

$sqlQuery = "select * from table2 where country in (". countryArray.join( "," ) .")"; // And bam!

But can certainly mix your two queries.


Hope that helps,

[/uber_quick_reply]

Okay - did a little digging. join is the same as implode, so I did this:

$sql = mysql_query("select city from cities where county = 'Macomb' OR county = 'Oakland' order by city asc");

$result = mysql_fetch_array($sql);
$check = implode(',', $result);

If I echo $check, I get the first item in the array printed twice. Such as
Addison Township,Addison Township

But if I do this:
while($result = mysql_fetch_array($sql)){
echo "$result[city]<br />\n";
}

I get everything, like I should. So, my implode is not correct. Also, if I use the 'in' example, do they need to be quoted?

You could just do it all in the one query

$sql = "SELECT c.city FROM table as t1, cities as c WHERE t1.status = 'enabled' AND t1.counties = c.counties ORDER BY c.city"

Just modify it to suit what you want, saves a hell of a lot of fucking around and code.

I kinda figured there was a way to do it in one query, but I'm a little slow on the syntax you provide...

Could you break it down a little or provide a resource where I can bone up on it? I'm stuck on the meaning of c and t1.

Thanks!

Basically what you are doing is querying multiple tables, so you make a reference to it basically so you dont have to type table.status = 'enabled'. It also helps because sometimes you need to query the one table in 2 ways, so you could do

table as t1, table as t2.

But really all it is doing is creating a name to refer to that table as.

So.... what i am doing in that statement is using the 2 tables you want results from, 'table' and 'cities' (you called it 'table' in your first post its probably something else).

Then we have a where statement, so WHERE t1.status = 'enabled' (This will select all of the results in 'table 'where status is enabled)

Next we do AND t1.counties = c.counties (From the the items that were enabled it will then match up the rows in 'table' with 'cities' wherever the counties field value is the same)

At the start i did SELECT c.cities (This is selecting the cities field from the 'cities' table where the rows match up with what we wanted.)

One thing i recommend is watching your notation, dont have a table called 'cities' and a field called 'cities' as well. And if you want better performance what i would do is.

In your CITIES table have the first row being CITY_ID, and then have another row being CITY_NAME. In your COUNTIES table, match up the CITY_ID, not the CITY_NAME. This will allow you to do faster queries and overall reduce your table size.

Okay, admittedly, I'm still a little slow on this. :-(

I have two tables: cities (with fields called 'city' and 'county') and counties (with fields called 'county' and 'enabled')

I need to select distinct cities from city where the county.enabled = 'on' in counties (if that makes sense)

I can feel the light bulb above my melon flickering as I can almost grasp this. But not quite.

Okay, I think I got it -

SELECT p.city, o.county from counties o, cities p where p.county = o.county and o.enabled = 'on'

That appears to give me the correct results.....

Yup looks like youve got it, its a big step learning this way i guess, but beleive me its worth it - opens up a whole new world of sql to you to make the database do the work. Means less coding too!

Keep in mind you arent limited to putting 2 tables in the one query you can have a bunch of them. If your version supports subqueries also then you should only ever need 1 sql statement, unfortunately mine doesnt atm

quote:

---

$sqlQuery = "select * from table2 where country in (". countryArray.join( "," ) .")"; // And bam!

---

Yeah BAM!! you'll get a nice error if you mix JavaScript syntax countryArray.join( "," ) within PHP code

True unless you can use this syntax with php5 since its more OOP? Im not sure havn't got that version going yet ;(

I don't know either for php5, but in php4 the OOP syntax was : $instace->method();

I would assume it remains the same

I'm going to bring this thread back up because I have a question regarding deletes. I have a large database, so I'm reluctant to just start trying things until I'm a little more sure I'm not going to be spending too much time restoring databases.....

I need to:
get all IDs from psor_offenders, then
delete from psor_aliases and psor_offenses where id is the same as the psor_offender id. (Hope this makes sense).

What I've been doing is just grabbing the id from psor_offenders, then cycling through two queries where I first delete from psor_aliases and then psor_offenses. I'm trying to combine everything into one query.

Right now, my daily maintenance involves nearly 30,000 queries. I have all of the features pretty much done and I'm just trying to optimize.

Thanks!

<edit - typo>



(Edited by Pugzly on 10-08-2005 08:37)

Complicated deletion queries is one thing i havnt done much of, but if you worried about your existing data make some dud data and be sure to put LIMIT statements at the end of your queries just in case! .

Of course the best way would be to set up some new tables to test with and get it working first, then just change the table names in your quereis.

Back to the querie, im guessing it would be something like

DELETE FROM psor_offenders as po, psor_aliassess as pa WHERE po.ID = pa.ID

NOT sure if you can used named tables like in a delete query but give it a try, its how you would get all the matching values in a select query.