Random SQL Update Issue

Question

I managed to fix my old error so I'm going to update this post with the new error. Basically I have to manually add a structure in phpmyadmin with my users ID in order for it to update using this code. Because I set it to update on whatever column has the same as the users id.

Any idea how I could do it differently so there is some sorta default automatically? I tried doing it so when you register it would do it and that works out well but I am going to have up to 10 different subjects so how would I do this in a more efficient way?

<?php
include 'connect.php';
include 'main.php';
$id = $_SESSION['id'];
$result3 = mysql_query("SELECT * FROM html WHERE id='$id'") or die("MYSQL Query Failed : " .mysql_error());
while($row3 = mysql_fetch_array($result3))
{
$lastpage=$row3['lastpage'];
}
$page = "page1.php";
$sql = "UPDATE html SET id='$id', lastpage='$page' WHERE id='$id'";
mysql_query($sql) or die("MYSQL Query Failed : " . mysql_error());
echo $lastpage;
?>

Another thing I'm trying to figure out is if I can make it check the database for the value under 'lastpage' like I did but then check if it is equal to page2 and above than it will not update the values at all. Basically it will only update the values if thats your first time going to the page on your account. Get it?

Anyone got any ideas?!

Do you think this would work?

<?php
include 'connect.php';
include 'main.php';
$id = $_SESSION['id'];
$result3 = mysql_query("SELECT * FROM html WHERE id='$id'") or die("MYSQL Query Failed : " . mysql_error());
while($row3 = mysql_fetch_array($result3))
{
$lastpage=$row3['lastpage'];
}
$page = "page1.php";
if(empty($lastpage)){
mysql_query("INSERT INTO `html`.`html` (`id`, `lastpage` VALUES ($id, $page");
} else {
$sql = "UPDATE html SET id='$id', lastpage='$page' WHERE id='$id'";
mysql_query($sql) or die("MYSQL Query Failed : " . mysql_error());
}
echo $lastpage;
?>

**By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. — Andy Lester, Sep 21 '13 at 02:55
I'm just testing around to try and make it work out. I just want to know what I did that is making this not work properly. Although I will read that because I need to learn more. Do you think you could show me an example of a more secure version of my code that is working? — Kevin Harrison, Sep 21 '13 at 02:57
You don't say what the error is, or what "not work properly" means. — Andy Lester, Sep 21 '13 at 02:59
I explained that in order for it to actually insert the data into the table html it has to find a column matching the session ID and insert it in there. This means that there has to be a default structure for every single user with the ID of their account inside the html table. Is there anyway I could just make it so it adds it no matter what and if there is something with a matching ID it will overwrite that column. If I didn't explain well enough please let me know. — Kevin Harrison, Sep 21 '13 at 03:01
It's not clear to me at all what it is you're wanting to do. — Andy Lester, Sep 21 '13 at 03:08
I want to insert $id and $page into the columns id and lastpage inside a table called html. But I want to make it so that if you revisit this page it will update it rather than just inserting it. Therefor it will overwrite the data you had lastpage saved as in the database before you visited the page. The problem is that it won't update the column with the id of the user because there is no column. I want it to automatically make a column and then update this data in it. Any idea? — Kevin Harrison, Sep 21 '13 at 03:12
Updated the post. Tell me if you think what I changed would work out? — Kevin Harrison, Sep 21 '13 at 03:14

score 0 · Accepted Answer · answered Sep 21 '13 at 03:25

0

Do it this way and you will be all right

 <?php
    include 'connect.php';
    include 'main.php';
    $id = $_SESSION['id'];
    $page = "page1.php";

    $sql_chk = " select * from html where id = '$id' and lastpage = '$page' ";
    $rs_chk = mysql_query($sql_chk);
    $num_chk = mysql_num_rows($rs_chk);

    if ($num_chk == 0) {
        mysql_query("INSERT INTO `html` (`id`, `lastpage`) VALUES ('$id', '$page') ");
    } else {
       $sql = "UPDATE html SET lastpage='$page' WHERE id='$id' ";
       mysql_query($sql) or die("MYSQL Query Failed : " . mysql_error());
    }
  ?>

answered Sep 21 '13 at 03:25

asarfraz

518
3
8

Do you know how I could make it so it checks the value in the database and if its equal to page2.php or above than it will not update any data. Any ideas? – Kevin Harrison Sep 21 '13 at 03:29
By the way this script won't overwrite data if there is already data in it. Any idea how to make it so I could make it overwrite data and also make it not overwrite data using an if statement. – Kevin Harrison Sep 21 '13 at 03:31
You can pass the $page variable from query string or check the $_SERVER['SCRIPT_NAME'] to check which page you are on. As for overwriting data you can place the check in the else condition to check for the condition to update data or not – asarfraz Sep 21 '13 at 04:30

Random SQL Update Issue

1 Answers1