[Wolves] yet another php/mysql question

[David Goodwin]

> ../update.php?id=43&name=bob
> 
> and it will pull up bob's record and allow edit and save.
> 
> All well and good, but I wanted to create an on the fly link during the insert phase but of course at that point you don't know the ID until record created so decided to use
> email and dob as unique items.
> 

My approach would be that if 'id' (or $_POST['id']) is not present, then you'd INSERT into the database, otherwise you'd do an update. So do a query first, and if it exists, do an update.

Alternatively, you could use http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html ?


> 1) or can you get id during the insert/save as a emailable variable - that's probably the easiest cos then other scripts untouched.?
> 

I don't understand what you mean as 'emailable variable'.


Assuming 'id' is an auto_increment field, then you can get hold of it after you've done the insert. If you're using the mysql_ api, your code could look like :

$success = mysql_query("INSERT INTO my_best_table (field, gate, hedge) VALUES ('big', 'one', 'prickly');
if($success) { 
    $id = mysql_insert_id();
    echo "Just added a new record Mum - it was $id !!";
}

> 2) what i have been mostly attempting to do is create a hyperlink containing email address and dob during insert phase eg
> ../update.php?email=xxx&dob=yyyy  so changed id to email ,  update.php receives this post data, opens the right record , got it set to display the correct ID.


OK. You ought to look at having a unique constraint within the database based on email & dob in this case.

Presumably you now have a query like "SELECT * FROM student WHERE email = 'blah at blah.com' AND dob = 'bl/ah/yyyy'" ?


> so id is set as $id - so update.php is populated with all the info needed as when using id and dob to populate.
> 

OK.

> Can I get the bugger to save the record to that ID can I hell?
> The ID seems to be present as it appears in update.php, do I need to do anything special to get it posted to record.php?
> 
> the bits that seem to matter:
> 
> update.php
> echo "<form method=\"POST\" action=\"updaterecordtest.php\">";
> echo " <tr>";
> echo " <td width=\"35%\">Diver ID</td>";
> echo " <td width=\"65%\"><input type=\"text\" name=\"id\" size=\"35\" value=\"".$row["id"]."\"></td>";
> 
> (shows correct ID)
> 
> record.php are:

updaterecordtest.php != record.php.

Put a 'var_dump($_POST);' followed by a 'die("grr")' or equivalent within the top of post.php and try submitting the form. 

This is a simple way to tell if the data is arriving as you expect in post.php.

> $id = $_POST["id"];
> 

You should really start casting your variables to make sure they are actually numbers when they should be.

Also, you need to look into using mysql_real_escape_string - else you'll find someone with an email address of blah.o'reilly at blah.com breaks your stuff; let alone the fact that your code is vulnerable to SQL injection.
When echo'ing stuff out you should make sure you sanitise it with e.g. htmlentities() to avoid Cross Site Scripting issues. (See http://php.net/htmlentities)


> and
>   if (isset($id))
>  {
>         $query = "UPDATE phonelist SET
>                                 coname = '$coname',
>                     address1 = '$address1' ,
>                    etc
> 
>                         WHERE
>                                 id = '$id'
>         ";
> 
> etc
> 
> (nothing recorded)


Hopefully the above is of some limited use?

thanks
David.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.lug.org.uk/pipermail/wolves/attachments/20120413/4d3b233f/attachment.htm>