[Wolves] yet another php/mysql question

[Wayne Morris]

On 14/04/2012 00:15, David Goodwin wrote:
>>
>
> 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 !!";
> }
>
That's the ticket ;-) Yahoo!
I couldn't get my head around how the user could save a new record, and 
the page generate a url based on two variables for the user to be able 
to edit ONLY their own record.
So I wanted url/ID&DOB - to edit someone else's record you'd need to 
know their database ID (not public apart during test) and DOB - secure 
enough for the basic records I keep.
just couldnt figure how to get ID...easy  when you have an example 
snippet doh.




>> 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 <mailto: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.
>

Ahh, thats useful!


>> $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 
> <mailto:blah.o%27reilly 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)
>
>
more reading ;-)

>> 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.
>
>
Excellent, thanks!

;-)

Wayne



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