Jump to content


Photo

How do i append a MySQL record id to a URL?


  • Please log in to reply
7 replies to this topic

#1 daniish

daniish
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 29 October 2006 - 01:01 PM

Hello,

I have a form where i would like to send a hidden variable. The value of the variable is a URL to display the input from the form. The input from the form is stored as a record in a MySQL database. I am using the following code:

Code:

<INPUT TYPE=hidden NAME=info_id VALUE="http://www.globexpos...more_info.html>



My question is:
How do i append the MySQL record 'id' to the URL so that it returns the relevant information? 
(I know the 'id' would be equal to the last 'id' in the database +1 but don't know how to code that)

Many thanks
Olly

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 29 October 2006 - 01:44 PM

I know the 'id' would be equal to the last 'id' in the database +1


That's the problem - you don't know that. In between your getting that value and using it, someone could insert another record.

Second problem - how are going to use that id in an html page?

If you want that id for inserting the next new record, use an auto_increment id in the table and let MySQL do it for you.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 daniish

daniish
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 29 October 2006 - 04:40 PM

Only i have access to add records to this database...so i can always check the database to confirm what the latest record id is.

I would like to have an html temlate (that uses php) to display the data entered on the form from the MySQL database using something like:

echo $row['title'];
echo $row['date'];
echo $row['description'];

where the 'id' from the form = the 'id' in the MySQL table (these would be the same). I already use auto_increment id in the table.

I am using Google Maps to display points i visit and i have form to enter details of these on the move. I need the link to put in the Marker label so that users can select this link (which i hope to have open in an iFrame i have on the same page) to get more information.

I hope you can help Barand!

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 29 October 2006 - 06:01 PM

To get the last id
<?php
$res = mysql_query("SELECT MAX(id) FROM tablename") or die(mysql_error());
$max_id = mysql_result ($res, 0, 0);
?>

If $id is the id of the record you want to link to (note it needs to be "more_info.php"),
<?php
echo "<form method='post' action='http://www.globexposure.net/more_info.php'>
    <INPUT TYPE='hidden'' NAME='info_id'' VALUE='$id'>
    <input type='submit' name='submit' value='More info...'>
    </form>";
?>

or, if you just want a link,
<?php
echo "<a href='http://www.globexposure.net/more_info.php?id=$id'>More info...'</a>";
?>


In "more_info.php", something like this
<?php
     $sql = "SELECT title, date, description 
            FROM tablename
            WHERE id = {$_POST['info_id']}";           // if link used, this will be $_GET instead of $_POST
     $res = mysql_query($sql) or die(mysql_error());
     list ($title, $date, $description) = mysql_fetch_row($res);
     
     echo "<table border='1' cellspacing='0' cellpadding='4'>\n";
     echo "<tr>
            <th>Title</th>
            <td>$title</td>
           </tr>
           <tr>
            <th>Date</th>
            <td>$date</td>
           </tr>
           <tr>
            <th>Description</th>
            <td>$description</td>
           </tr>
           ";
     echo '</table>';
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 daniish

daniish
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 29 October 2006 - 07:40 PM

Many thanks Barand thats very helpful.

If i could trouble you for one last point:

On my current form which adds my input to the MySQL database, if i wanted to include a short snippet of php to store the link in a field within the database...am i on the right path:

<form name="add_blog" action="insert_poi.php" method="post">

<?php
$res = mysql_query("SELECT MAX(id) FROM locations") or die(mysql_error());
$max_id = mysql_result ($res, 0, 0);
$new_id = $max_id ++1;
?>

<INPUT TYPE=hidden ID=info_id NAME=info_id VALUE="<a href='http://www.globexposure.net/more_info.html?id=$new_id'>More info...</a>" />

insert_poi.php looks like this:
<?php
$link = mysql_connect("a", "b", "c");
if (!$link)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("d", $link);

$sql="INSERT INTO locations (title, date, description, lat, lon, link, marker, info_id)
VALUES
('$_POST[title]','$_POST[date]','$_POST[description]','$_POST[lat]','$_POST[lon]','$_POST[link]','$_POST[itype]','$_POST[info_id]')";

if (!mysql_query($sql,$link))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added ";

echo "<a href='http://www.globexposure.net/form_index.html'>Back to Form Index</a>";

mysql_close($link)
?>

Again thank you.


#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 29 October 2006 - 08:16 PM

I don't understand the question. You already store a link value.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 daniish

daniish
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 30 October 2006 - 07:34 AM

Essentially within Google API i don't understand how to parse the 'id' of the label to retrieve relevant fields from my database for display in the iFrame. As a workaround i would like to store a url which references the specific record 'id' within a field in my database.

I know how to get the contents of this field to display in my Google Maps label, which will provide the necessary link without understanding the above.

For this to work i need my initial form (add_poi_form.php) to collect this information as a hidden variable and i can use your code to work out what the MAX(id) is - but i need MAX(id) +1
I think this would be done in the following sort of way but i'm really not sure:

<form name="add_blog" action="insert_poi.php" method="post">

<?php
$res = mysql_query("SELECT MAX(id) FROM locations") or die(mysql_error());
$max_id = mysql_result ($res, 0, 0);
$info_id = $max_id ++1;
?>

<INPUT TYPE=hidden ID=info_id NAME=info_id VALUE="<a href='http://www.globexposure.net/more_info.html?id=$info_id'>More info...</a>" />


Hopefully i haven't confused the situation and i'm sorry my question is so convoluted but i would appreciate your clarity.

Thanks
Olly

#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 30 October 2006 - 08:05 PM

Is it an individual static html page for each item?

Or is it always the same dynamic php page which will display all items depending on the id passed to it?

If it's the former, there is no point passing the id.

If it's the latter, all you need is the id, which is in the record already, and so link is unnecessary.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users