Problem with mysql_insert_id

[Gary_Kambic]

Hi everyone,

I'm trying to insert information into my table which it is doing fine. But I need the id of the inserted record so I can update associated tables. The function mysql_insert_db($dbh) always returns a value of zero (0), why is this? Is there some better way of getting the id from the inserted record?

Thanks very much, Gary

$user = "username";
$password = "password";
$database = "dbname";
$dbh = mysql_connect(mysqldb,$user,$password);
mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);

$query = "INSERT INTO agents VALUES ('', '$agencytype', '$agencyname', '$agencyaddress1', '$agencyaddress2', '$agencycity', '$agencystate', '$agencyzipcode', '$agentname', '$agenttelephone', '$agentfax', '$agentemailaddress' , '$agentwebsite', '$agentsignupdate', '$datejulian', '')";

$agentid = mysql_insert_id($dbh);

mysql_query($query);

[bastien]

couple thing i noticed...see bold

$user = "username"; 
$password = "password"; 
$database = "dbname"; 
$dbh = mysql_connect(mysqldb,$user,$password); 
mysql_select_db($database) or die( "Unable to select database"); 
//mysql_query($query);  [b] why have a query here?[/b]


//[b]form sql statement[/b]
$query = "INSERT INTO agents VALUES ('', '$agencytype', '$agencyname', '$agencyaddress1', '$agencyaddress2', '$agencycity', '$agencystate', '$agencyzipcode', '$agentname', '$agenttelephone', '$agentfax', '$agentemailaddress' , '$agentwebsite', '$agentsignupdate', '$datejulian', '')"; 

//[b]first run sql statement[/b]
mysql_query($query) or die ("Can't connect because ".mysql_error());

//[b]then get inserted id [/b]
$agentid = mysql_insert_id($dbh);

[Gary_Kambic]

Thanks, it works great! I really appreciate the help. Gary

[Gary_Kambic]

Thanks for being so helpful on the last question.

On a related question. I've been researching the fetch command but not sure it is the correct command to use for what I want to do.

Right now I have a variable $id which contains the record id for the client I need to find in the "clients" table. I need the "agent_id" which is a field in the record of this client. Right now, I'm searching through the entire "clients" table with a While statement, then comparing the id's ($id to record id_table). Since I already know the id of the client record, can't I search just for the particular record with the $id as the key? Which command would I use for this? It's got me pretty confused.

Thanks again for your help, Gary

$query="SELECT * FROM clients";
$result=mysql_query($query);
$num=mysql_numrows($result);
echo "client id(1): " ."$id" . "<br>";
$i=0;

while ($i < $num) {
$id_table = mysql_result($result,$i,"id");
if ($id_table == $id) {
$client_email_address = mysql_result($result,$i,"client_email_address");
$agent_id = mysql_result($result,$i,"agent_id");
$i = $num; }
else
++$i;
}

[bastien]

hey gary,

just use the where clause in the sql statement

$query="SELECT * FROM clients where id=$id";

now if the $id value is numeric then no single quotes are needed to make the statement work....should the value be alphanumeric then you need to add single quotes around the variable like this:

$query="SELECT * FROM clients where id= '$id'";

also note that if your variable is a portion of the word (like john) you can use the wildcard symbol denoted by the % sign to make matches against if used after the variable like this:

$query="SELECT * FROM clients where id= '$id%'";

johns
john
johnson

and will pick up

sigjohns

if the % is before the var like this:

$query="SELECT * FROM clients where id= '%$id'";

using the % before and after the value will match all of the above...

$query="SELECT * FROM clients where id= '%$id%'";

hth

[Gary_Kambic]

Bastien,

hey thanks for all the info, it will be a much more efficient way to go.

By the way, I like the quote "Cat, the other other white meat."

Makes me smile! :-)

Take care,

Gary

[Gary_Kambic]

I spoke too soon . . .

I replaced the code with:

$query="SELECT * FROM clients where id=$id";
$result=mysql_query($query);
$client_email_address = mysql_result($result,$i,"client_email_address");
$agent_id = mysql_result($result,$i,"agent_id");

The $client_email_address and agent_id are coming back blank.

Now I know when I was searching in the loop that $i was equal to something. What should replace $i ? Or do I need a different statement type here instead of mysql_result?

what fun, Gary

[bastien]

what is the value of the $id...where is it retreived from (a url, another query etc)?

[Gary_Kambic]

OK, I changed some code and got the it to this point: The following info comes from protection.php and is passed to menu.php

client id: 8
agent_id: 5

menu.php --- The following code works, but I'd like to be able to retrieve the record with the where instead of searching the entire table:

echo "client id: " ."$id" . "<br>";
echo "agent_id: " . "$agent_id" . "<br>";

$query="SELECT * FROM agents";
$result=mysql_query($query);
$num=mysql_numrows($result);

$i=0;

while ($i < $num) {
$id_table = mysql_result($result,$i,"id");
if ($id_table == $agent_id) {
$id = mysql_result($result,$i,"id");
$agency_type = mysql_result($result,$i,"agency_type");
$agency_name = mysql_result($result,$i,"agency_name");
$agency_address_1 = mysql_result($result,$i,"agency_address_1");
$agency_address_2 = mysql_result($result,$i,"agency_address_2");
$agency_city = mysql_result($result,$i,"agency_city");
$agency_state = mysql_result($result,$i,"agency_state");
$agency_zip_code = mysql_result
$i = $num; }
else
++$i;
}

I know I can use:

$query="SELECT * FROM clients where id=$agent_id";
based on what you said. But I think the mysql_result needs to be replaced. The $i needs to be replaced.

I guess I'm just asking how to do the above but by using the "where" instead of searching.

Thanks again,

Gary

[Weedpacket]

Originally posted by Gary Kambic
Now I know when I was searching in the loop that $i was equal to something. What should replace $i ? Or do I need a different statement type here instead of mysql_result?

$i was counting the rows of the result set; there will only be one row (I figure), number 0. So that's what you'd replace $i with.

As far as other functions go, you'll get better performance out of using [man]mysql_fetch_array/man or one of the other mysql_fetch_* functions (simply because you'd be pulling stuff out of the result set once instead of half a dozen times).

Also, another speed improvement can come from stating the precise fields you want to retrieve from the table in the SELECT statement, instead of just asking for "*".

(And, as a last observation, the while loop would have been better written as a for loop. Tidier, and just a tick faster.)

[bastien]

$query="SELECT * FROM agents where id = $agent_id";

$result=mysql_query($query) or die ("Can't connect because ".mysql_error());

if (!$result){
   echo "no rows returned";

}else{

   //pick the fields you want to show the user and eccho them out

   //show the table headers
   echo "<table><tr>";

   //get the number of fields
   $num = mysql_num_fields($result);

   for ($i=0; $i<$num;$i++){
        echo "<th>".mysql_field_name[$i]."</th>";
   }

echo "</tr><tr>";

while ($row = mysql_fetch_assoc($result)) {
    echo $row["agent_id"];
    echo $row["agent_name"];
    echo $row["agent_location"];

    //etc
}

some of the above is kinda optional, like the table headers...you may just want to fix them in html instead of showing the column names frim the db...though the above does give greater flexibilty when the paramters change

hth

[Gary_Kambic]

Thanks guys for all your help. I implemented all of your suggestions and it does indeed run faster.

Thanks again, Gary