Connecting Tech Pros Worldwide Help | Site Map

Looking up column values for an array of tables

  #1  
Old June 16th, 2009, 07:27 PM
Newbie
 
Join Date: May 2009
Posts: 21
Hello,

In PHP, I have an array called $table_list[]. It contains a list of MySQL table names. Each one of these tables has a column called "site" and one called "votes_up."

For each table in $table_list[], I would like to look up the value of "votes_up" when "site"=$entry.

How could I do this using PHP and MySQL?

Thanks in advance,

John
  #2  
Old June 16th, 2009, 07:41 PM
Atli's Avatar
Moderator
 
Join Date: Nov 2006
Location: Iceland
Posts: 3,701
Provided Answers: 4

re: Looking up column values for an array of tables


Hi.

Check out the foreach loop in the manual.
The idea is: loop though your array and execute a query for each of your tables while collecting the results into a second array (or just printing them, whichever works for you.)

Try it out. If you run into any problems, show us what you've done and we'll help.

Last edited by Atli; June 16th, 2009 at 07:42 PM. Reason: Spelling
  #3  
Old June 16th, 2009, 11:15 PM
Newbie
 
Join Date: May 2009
Posts: 21

re: Looking up column values for an array of tables


The code below is close. What it does is return the following:

Quote:
Table Name 1
Table Name 2
Table Name 3
Table Name 4
"$entry": "votes_up for $entry from Table Name 4"
What I want is:

Quote:
Table Name 1: "votes_up for $entry from Table Name 1"
Table Name 2: "votes_up for $entry from Table Name 2"
Table Name 3: "votes_up for $entry from Table Name 3"
Table Name 4: "votes_up for $entry from Table Name 4"
How could I change the code to make it return what I want?

Thanks,

John

Expand|Select|Wrap|Line Numbers
  1. $result = mysql_query("SHOW TABLES FROM feather") 
  2. or die(mysql_error()); 
  3.  
  4. while(list($table)= mysql_fetch_row($result))
  5. {
  6.  $sqlA = "SELECT `site`,votes_up FROM `$table` WHERE `site` LIKE '$entry'";
  7.  $resA = mysql_query($sqlA) or die("$sqlA:".mysql_error());
  8.  if(mysql_num_rows($resA) > 0)
  9.  {
  10. $table_list[] = $table;
  11. while($rowA = mysql_fetch_assoc($resA))
  12.   {
  13.   $votes_up[$rowA["site"]] = $rowA["votes_up"];
  14.   }
  15.  }
  16. }
  17.  
  18. foreach( $table_list as $key => $value){
  19.     echo "$value <br />";
  20. }
  21.  
  22. foreach($votes_up as $site => $vote_up)
  23. {
  24.   echo "$site: $vote_up";
  25. }
  #4  
Old June 17th, 2009, 01:48 AM
Newbie
 
Join Date: May 2009
Posts: 21

re: Looking up column values for an array of tables


Nevermind.

This gives me what I want:

Expand|Select|Wrap|Line Numbers
  1. $result = mysql_query("SHOW TABLES FROM feather") 
  2. or die(mysql_error()); 
  3.  
  4. while(list($table)= mysql_fetch_row($result))
  5. {
  6.   $sqlA = "SELECT COUNT(*) FROM `$table` WHERE `site` LIKE '$entry'";
  7.   $resA = mysql_query($sqlA) or die("$sqlA:".mysql_error());
  8.   list($isThere) = mysql_fetch_row($resA);
  9.   if ($isThere)
  10.   {
  11.      $table_list[] = $table;
  12.   }
  13. }
  14.  
  15.  
  16. foreach ($table_list as $table) { 
  17.     $sql = "SELECT votes_up FROM `$table` WHERE `site` LIKE '$entry'"; 
  18.     $sql1 = mysql_query($sql) or die("$sql:".mysql_error());
  19.    while ($row = mysql_fetch_assoc($sql1)) {
  20.        echo $table . ': "' . $row['votes_up'] . " for $entry from $table\"<br />";
  21.    } 
  22. }
  #5  
Old June 17th, 2009, 08:08 AM
Atli's Avatar
Moderator
 
Join Date: Nov 2006
Location: Iceland
Posts: 3,701
Provided Answers: 4

re: Looking up column values for an array of tables


I'm glad you found a solution :)
Post again in you run into any other problems you need help with!
Reply


Similar Threads
Thread Thread Starter Forum Replies Last Post
Looking For A Better Way Wayne Wengert answers 7 November 21st, 2005 11:07 PM
comp.lang.c Answers to Frequently Asked Questions (FAQ List) Steve Summit answers 0 November 13th, 2005 03:15 AM
Query looking back Diamondback answers 4 July 23rd, 2005 08:05 AM
Remove items from GetRows array Patrick G. answers 2 July 19th, 2005 12:10 PM