php/mysql query help please.

paul_m

This is probably an easy one, but my brain is frazzled and I need to get this done.

I have two tables in one databse.

One is a list of shops with information such as name and address etc.

The other table contains submitted information about those shops, including two fields, one that is set to '1' or true when the customer thinks that overall the shop is "good" and another that is set to '1' or true if the customer feels overall the shop is 'not good'.

I would like some advice on the most efficient way to run through all the shop_ids and count the number of 'good' and 'not good' "votes" each shop has recieved, and store this in an array for displaying. Ie an array for each shop with the number of good and bad votes stored in the array to use later in the script.

I know I could use several queries to achieve this, but I am sure there must be a clean and efficient way of doing this.

Any help much appreciated.

Paul

sarah

It would be easier if instead of a good column and a bad column, you had a single vote column where (for example) 1=good and 2=bad, but here's how I think you can accomplish what you want with the way it is...

while(mysql_fetch_rows($result){
     $storeID=$row[whatever];
     $good=$row[whatever];
     if($good==1){
          $vote="good";
     }else{
          $vote="bad";
     }
     if(isset(${"$storeID_$vote"})){
          ${"$storeID_$vote"}++;
     }else{
          ${"$storeID_$vote"}=1;
     }
}

The result (if I typed it out correctly) is a bunch of variables made up of the store name and good or bad like...
$24_good=3
$24_bad=2
$46_good=6
$46_bad=4

altexis

why don't you choose the proper way of doing this?

having only 1 column and use the value "1" for good and "0" for bad

then you can use only two SQL queries to complete your job

SELECT COUNT(*) FROM shops
..that will count all the shops (a very fast query)

SELECT COUNT(*) FROM shops WHERE vote="1"
..that will count the "good" votes (a very fast query if you have indexed the "vote" column

so the result from the second query will give you the good votes
and the subtraction of the two will give you the bad votes

paul_m

Thanks, I'll give it a go.

One extre problem (which I didn't explain well) is that the store names are in the first table (not the table the databases are in) and I want to be able to list all stores, by name, and how many good and bad votes they get.

Birminham store 24 good 4 bad
Nottingham store 2 good 43 bad
etc.

So what I would like to know is what is the best way to grab the name of the store at the same time as the votes, which are (of course) in two different tables?

Thanks again.

Paul (confused by joining etc.)

altexis

sorry... missed the voting table

but its all the same. if the votes table has a column "shopID" showing the corresponding shop for a vote and a "vote" column then:

SELECT COUNT(*) FROM shop_votes WHERE vote="1" GROUP BY shopID

for the good votes, and

SELECT COUNT(*) FROM shop_votes GROUP BY shopID
for the total votes

then with the help of an array you can find the results

I think this is the fastest approach

paul_m

Thank you.

I should sleep more when trying to do things like this. 🙂

sarah

You can do the query separately. In this example, you have just selected all of the rows from the store table and you're ready to print out the votes...

while(mysql_fetch_rows($result){
     $storeName=$row[whatever];
     $storeID=$row[whatever];
     if(!isset(${"$storeID_good"})){
          $good=0;
     }else{
          $good=${"$storeID_good"};
     }
     if(!isset(${"$storeID_bad"})){
          $bad=0;
     }
     }else{
          $bad=${"$storeID_bad"};
     }

 print("$storeName $good good $bad bad");
}

Again...beware-o-typos

paul_m

Originally posted by sarah
You can do the query separately. In this example, you have just selected all of the rows from the store table and you're ready to print out the votes...
while(mysql_fetch_rows($result){
     $storeName=$row[whatever];
     $storeID=$row[whatever];
     if(!isset(${"$storeID_good"})){
          $good=0;
     }else{
          $good=${"$storeID_good"};
     }
     if(!isset(${"$storeID_bad"})){
          $bad=0;
     }
     }else{
          $bad=${"$storeID_bad"};
     }

 print("$storeName $good good $bad bad");
}
Again...beware-o-typos [/B]

That's how I did it, but with 200+ stores I didn't want to be hammering mysql too much?

sarah

You got in right as I was reading altexis's reply...I'd give his a go...

paul_m

Thank you both.

🙂