Using multiple SELECT

[dreamwest]

Im doing something wrong here...i just cant work this out.

 

I need to select a table with columns and use the $row data to output results, then  i want to select another table with columns but with a differnt query:

 

$result = mysql_query("SELECT * FROM photo_import WHERE id_pics='15' ")or die(mysql_error());
    $row = mysql_fetch_array( $result );
echo $row['id_pics'];
   
///Now select a new query but use data from the first query within the SELECT

$result = mysql_query("SELECT * FROM ratings WHERE categories_pics = '".$row['id_pics']."' AND download_pics='1' ORDER BY RAND() LIMIT 20")or die(mysql_error());

while($row = mysql_fetch_array($result)){

echo $row['title'];


}

 

As you can see both queries conditions are different .

The first query works but the second doesnt produce any results.

 

 

 

[corbin]

Have you tried echo'ing the query to make sure it's what you would expect?

[dreamwest]

Yes, both queries are producing the required results but when together one is interfering with the other. I cant figure out whats going on

[fenway]

Well, echo them for our benefit, too.

 

You might want to avoid using the same variable, though.

[mpiekarski]

Hi,

 

  First of all let me start by saying that I don't want to come off the wrong way. I am not a database or even a php "expert" but I do use both fairly often.  However, in my trials and tribulations with PHP, I think I see a few things that could use some help here:

 

I noticed that you are using the old PHP MySQL Class.  Not that this is necessarily a horrible thing, but MySQLI and PDO::MySQL you will likely find MUCH faster, easier to write and just over-all cleaner than the old versions.  I am more of an Object Oriented guy myself.  If procedural coding is your thing, great.  Otherwise, check out http://www.php.net/mysqli and http://www.php.net/pdo::mysql. 

 

Additionally, I think you should also brush up on your SQL Syntax.  If you are doing a select based on the output of another query, a Join is your saviour.  A few simple sites that I used to get a hang of simply MySQL Stuff such as joins were:

 

http://www.geekgirls.com/menu_databases.htm

http://www.tutorialspoint.com/mysql/mysql-using-joins.htm

http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

http://dev.mysql.com/doc/refman/5.0/en/join.html

 

 

A tip you want to think of when making a database-powered application, is balance.  You don't want to do anything extra with your web server than you have to.  A join will take care of sorting out all of your results and plugging them into parent query.  This way, you will add balance between the web server running php and the database server running MySQL. Think of a join like using the ` ((BackTick)) characters on the linux command line.  It is the same concept... For example:

 

mpiekarski@doakes:~$ uname -r
2.6.27-11-generic
mpiekarski@doakes:~$ echo "/lib/modules/`uname -r`/kernel/drivers"
/lib/modules/2.6.27-11-generic/kernel/drivers
mpiekarski@doakes:~$ ls "/lib/modules/`uname -r`/kernel/drivers"
acpi        char         firmware  infiniband  mfd      pcmcia  telephony
ata         clocksource  gpio      input       misc     power   uio
atm         cpufreq      gpu       isdn        mmc      rtc     usb
auxdisplay  crypto       hid       leds        mtd      scsi    video
block       dca          hwmon     md          net      serial  virtio
bluetooth   dma          i2c       media       parport  spi     w1
cdrom       edac         ieee1394  message     pci      ssb     watchdog

 

As you can see above, I used `uname -r` to plugin my currently loaded kernel version while listing some of the modules for that kernel.  Without getting into too big of a rant on Joins, I think that is your ticket.  See the links I provided above for more details.

 

I hope my explanation shows you why a Join statement might be more appropriate.  I also hope you give MySQLi A try.  I 100% suggest it over the old php_mysql.  Let me know if you have any questions or if I was in any way unclear.  Thanks!

[FezEvils]

it's a easy thing..try this

 

$result = mysql_query("SELECT * FROM photo_import WHERE id_pics='15' ")or die(mysql_error());

    $row = mysql_fetch_array( $result );

$idpics= $row['id_pics'];

 

$result = mysql_query("SELECT * FROM ratings WHERE categories_pics = '$idpics' AND download_pics='1' ORDER BY RAND() LIMIT 20")or die(mysql_error());

 

it would be easy if you using variable for value passing..hope it would help