Get ID from database ?

MichaelHe

I have a guestbook where the message is inserted in a database like this :

$sql = "insert into gaestebog(navn,epost,besked,ip,tid)
		values('$navn','$epost','$besked','$ip',now())";

Later in the file an email is sent to me like this :

mail($admin_epost, "Der er skrevet i din gæstebog!", $epostens_indhold);

I need the ID number from the database to be sent to me in this email.. how do I do that ?
I can't figure out how to get the ID number from this specific message.

Thanks in advance
michael

void_function

Immediately after your INSERT query, get its ID with mysql_insert_id() function:

http://www.php.net/manual/en/function.mysql-insert-id.php

You may wish to LOCK your table before the insert and UNLOCK it after the mysql_insert_id() call.

MichaelHe

Thank you very much.
Now I can get the ID, but

when I later in the line :

mail($admin_epost, "Der er skrevet i din gæstebog!", $epostens_indhold);

now write :

mail($admin_epost, "Der er skrevet i din gæstebog!", $epostens_indhold, $idnum);

The idnum isn't in the mail i receive.
I can write echo $idnum; right after the line

$idnum = mysql_insert_id();

and it print the id on the screen.
But it will not send it in the mail... Why is that.

void_function

Because you should send the id in the body of the email, right?

See http://www.php.net/manual/en/function.mail.php for mail() function reference.

If you wish to insert the ID in the body of the message, then use the $idnum variable in the message string ($epostens_indhold), for example:

$epostens_indhold.= "\n\nThe ID of the guestbook entry is $idnum.";
mail($admin_epost, "Der er skrevet i din gæstebog!", $epostens_indhold);

MichaelHe

OK.. but still it doesn't work like I won't it to..

The think I want is a link called www.domanename.dk/Gbog/Valid.php?idnum=14 ex. written in my email.
With this link I can then send the ID number to the valid.php site, where I shall use this number.

But when I write this :

www.all-shook-up.dk/Gbog.php?ID=$idnum

in the $epostens_indhold the email I get, have written www.all-shook-up.dk/Gbog.php?ID=
The idnum is not written. Why is that.
I'm lost..

void_function

If you wrote that after you fetched the $idnum, then it should work. If you can echo the $idnum value, then you can also use it in a string. Also, how exactly did you write it? Check your php_error_log for possible explanation of the error.

MichaelHe

To be exact.
I have a sql query inserting the geustbook message into the database

$sql = "insert into guestbook(name,email,message,ip,time)
		values('$name','$email','$message','$ip',now())";

Then I write:

$idnum = mysql_insert_id();

If I writen echo $idnum; after this line it writes the id number as it should. So the $idnum must work.

Then I write

$ip = getenv('REMOTE_ADDR');
$email_content = "www.all-shook-up.dk/Valid.php?id=$idnum - $name has written this message from IP-adress $REMOTE_ADDR:\n\n$message";

mail($admin_epost, "A message has been written in your guestbook!", $email_content);

The email I then receive has the message, name and ip correct written, but the www adress is not correct. It writes www.all-shook-up.dk/Valid.php?id=
Somehow the $idnum is not shown in the link.
As I see it it should work, so I just can't figure out why it doesn't.

Really hope you can help.

planetsim

And is that $sql variable being put into the [man]mysql_query[/man] function?

MichaelHe

Yes. That part works fine.. the message is being stored in the database as it should.

void_function

You got some weird error there... 😕

Check that you don't reset or otherwise change the $idnum between echoing it and using it in the email message string. And check for typos... I see that you've written the translated code here as example, but still check it in your code.

Also, to be on the safe side, use the resource identifier in the mysql_insert_id().

What is the column type of ID in the table?

Did you check the php_error_log? Maybe the error is listed there...

MichaelHe

Where do I find a php_error_log ??

Anyway I have check for mistypes, and can't find any reset either, so I'm still lost.

Here are my codes :

gem.php (the file that put data in the database and sends an email

<?php
require("./gbog-ting/mysql-funktioner.php");
require("./gbog-ting/design-funktioner.php");
require("./gbog-ting/indstillinger.php");

if(is_array($HTTP_POST_VARS)) {
  $navn = $HTTP_POST_VARS['navn'];
  $epost = $HTTP_POST_VARS['epost'];
  $besked = $HTTP_POST_VARS['besked'];
} elseif (is_array($_POST)) {
  $navn = $_POST['navn'];
  $epost = $_POST['epost'];
  $besked = $_POST['epost'];
}

function skriv_i_gbog($navn, $epost, $besked) {

$ip = getenv('REMOTE_ADDR');

$sql = "insert into gaestebog(navn,epost,besked,ip,tid)
	values('$navn','$epost','$besked','$ip',now())";

aabn_forbindelse_og_vaelgdb();
sql_spoerg($sql);
$idnum = mysql_insert_id();
luk_forbindelse();

}

skriv_i_gbog(addslashes($navn),addslashes($epost),addslashes($besked));
header("Location: ../Gbog.php");

if($epost_p == "t") {

  $ip = getenv('REMOTE_ADDR');
  $epostens_indhold = "www.all-shook-up.dk/Gbog/Valid.php?ID=$idnum $navn har skrevet denne besked fra IP-adressen $REMOTE_ADDR:\n-----------------------------------------------------\n\n$besked";

  mail($admin_epost, "Der er skrevet i din gæstebog!", $epostens_indhold);

}
?>

Here are the 3 files require files:

mysql-funktioner.php:

<?php 
function aabn_forbindelse_og_vaelgdb() {
  $server = "db.all-shook-up.dk";
  $bruger = "my username";
  $kodeord = "my password";
  $database = "my database name";

  if (!mysql_connect("$server","$bruger","$kodeord")) {
    udskriv_fejl("Kunne ikke oprette en forbindelse til MySQL.");
  }

  if(!mysql_select_db("$database")) {
    udskriv_fejl("Kunne ikke vælge databasen: $database");
  }
}

function sql_spoerg_og_faa_svar($query) {
  $resultat = mysql_query($query);

  if(!$resultat) {
    udskriv_fejl("Kunne ikke udføre: <em>$query</em>");
  }

  while($raekke = mysql_fetch_array($resultat)) {
    $resultat_array[] = $raekke;
  }

  return $resultat_array;
}

function sql_spoerg($query) { 
  $resultat = mysql_query($query);

  if(!$resultat) {
    udskriv_fejl("Kunne ikke udføre: <em>$query</em>");
  }
}

function luk_forbindelse() {
  if(!mysql_close()) {
    udskriv_fejl("Kunne ikke lukke forbindelsen til MySQL!");
  }
}

function udskriv_fejl($fejl) {
  echo "<p>$fejl";

  if ($mysql_fejl = mysql_error()) {
    echo "<br>Fejlen er: <em>$mysql_fejl</em>";
  }

  exit;
}
?>

Design-funktioner.php

<?php
function top($titel) {
	$html = "<html><head><title>$titel</title><link rel=\"stylesheet\" type=\"text/css\" href=\"Gbog/gbog-ting/stil.css\"></head><body>
		<h2>$titel</h2>";

return $html;
}
function bund() {
	$html = "</body></html>";
	return $html;
}

indstillinger.php

<?php
# "t" true - "f" False
$html_p = "f";
$antal_ad_gangen = 10;
$tabel_bredde = 450;
$epost_p = "t";
$admin_epost = "allshookup@mail.dk";
?>

I have tried it one more when the codes are exactly like abowe, and still the link say ID= and the number is missing.

void_function

Ah, this explains it all! Your $idnum is local to skriv_i_gbog($navn, $epost, $besked) function! Declare the variable outside any function, by setting it to some value, like 0:

$idnum= 0;

Then, when using it inside the skriv_i_gbog function (or any other function as well), you must redeclare it with:

global $idnum;

at the very beginning of the function, so that the parser could know that you're referencing to global $idnum, not to a local instance with the same name.

This should work. 😉

EDIT: php_error_log should, usually, be found just outside your htdocs directory. If not, ask your ISP where it is. Check it often, because it may grow fast, if you have errors or warnings.

MichaelHe

Thank you very very lock.
I didn't know that, but now it works like a charm 😉

Thanks for all your help.