Page 1 of 1

query pains

Posted: Sun Jan 14, 2007 1:52 pm
by mantis_61
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi All,

Glad to find other skilled web designers that are willing to share tips, code, and problems.

I've been using php for a little over 6 months now and it has been my first language. The entire time I have been having all kinds of troubles with the  mysql query function. I'm trying to use the mysql count() function to tally up the rows then store that value in a variable. My script did not work. I then broke my code down with some if statements and echos to confirm the query was working. It stated the query is successful however when I echo my result I usually get one of these 2:

Resource id#3
Resource id#5

?????????????

Heres the code:

Code: Select all

$link = mysql_connect('localhost', 'joe');
       if (!$link) {
	  die('could not connect to catalog' . mysql_error());
       }
       echo "<center><font color=#aa0000><i>you have connected to the catalog.</i></font></center>";
       echo "<a href='/main.html'>MAIN MENU</a>";
       mysql_select_db('db_one', $link);


      $tmp = $_FILES['userfile']['tmp_name'];
      

      $sql = "insert into {$_POST['type']} (id, name, seasonal, quantity, description, price, ext, pic_id) values (0, '{$_POST[name]}', default, {$_POST['quantity']}, '{$_POST[description]}', {$_POST['price']}, '$mimeType', LAST_INSERT_ID())";
      $result = mysql_query($sql);
The previous query works fine. However, this is the one that doesn't work:

Code: Select all

$sql = "select count() from {$_POST['type']}";
      $result = mysql_query($sql);
      $id = mysql_result($result);
      echo $id;                          // this is what always displays Resource id#3 || #5
      
      
      $folder = $_POST['type'];
      $destination = "uploads\\" . $folder . "\\" . $id . "." . $mimeType;
From what I have read you are suppose to use the mysql_result() function if you are trying to select a single field? I'm actually just trying to get the last auto_incremented id inserted for the pertaining table. Is there a better function to use to get this information. I tried storing different column selects into the mysql_fetch_array() function. This did not work either. I'm sure there is another way to do this and I'm sure there are many who understand why this one doesn't work. I have tried researching the limitations to the functions mysql_query in different contexts but can't seem to find a good reference. Does anyone know where I can find more understanding for mysql functions? Php.net is very vague. Any help would be appreciated thanks...

Mantis_61


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Sun Jan 14, 2007 2:09 pm
by Kieran Huggins
I use the following structure:

Code: Select all

$sql = "SELECT * FROM table WHERE `bob`='builder'";
$result = mysql_query($sql);

while($row=mysql_fetch_assoc($result)){
    echo $row['name']; // bob
    echo $row['job']; // builder
    echo $row['hat_color']; // yellow
}
also, take a look at mysql_insert_id()

Posted: Sun Jan 14, 2007 2:10 pm
by Burrito
have a look at mysql_insert_id() for the id of the last row created.

Posted: Sun Jan 14, 2007 2:31 pm
by mantis_61
Well I just got it to work for me by using the mysql_num_row() function.
I chose to not use mysql_insert_id because this function returns the last inserted id of the entire database not just the table. Instead this is what I did...


$sql = "select * from necklaces";
$result = mysql_query($sql);
$id = mysql_num_rows($result);
echo mysql_num_rows($result);

This also echoed fine. I don't really understand the concept that the result to a mysql_query() is not the actual db data of the corresponding field instead I see it called a set??? Maybe I'm still misinterpreting this. Is this why you cannot echo the $result? I don't understand why it gives me a resource id? What are these resource id's anyway. I know they are errors but what kind of errors?

Posted: Sun Jan 14, 2007 2:51 pm
by mantis_61
Thanks for the format Huggins. That one is working for me as well. :D