Updating database

Question

BadManSam 0 Light Poster

11 Years Ago

<?php 
include("include/session.php"); 
?>


<?php
$db = new PDO('mysql:host=localhost;dbname=******x2_login', '******x2_login', '********');

$stmt = $db->query('SELECT value FROM settings WHERE value = "Login Script" ');
$id = $stmt->fetchColumn(0);
if ($id !== false) {
    echo $id;
} ?>

<?php
    if(isset($_POST['Submit'])){//if the submit button is clicked


    $update = "UPDATE settings WHERE value = 'Login Script' SET value = '$value' ";

    $db->query($update) or die("Cannot update");//update or error

    }
?>

<html>
<form action="" method="post">


<table border="0" cellspacing="10">
<tr>
<td>Name:</td> <td><input type="text" name="value" size="100" value=""></td>
</tr>
<tr>
<td><INPUT TYPE="Submit" VALUE="Update the Record" NAME="Submit"></td>
</tr>
</table>
</form>

</html>



<?php
    if($update){//if the update worked

    echo "<b>Update successful!</b>";



}  

?>

Hi, I'm trying to update a row of a databse, a certain record. When I click submit, I get cannot update (as it says in the script.) The top part (php) gets the value of the record, please could someone tell me how to display the actual value of the row, so when it changes it displays the changed record.

In the databse I have two colums, name and value, I have 1 row, in the column 'name' I have SITE_NAME and the column next to it 'value', in the row I have: 'Login Script'. I don't know how to make it so that it displays whatever the text is in the row. It's the first row.

I also can't get it to update the record when 'Submit' is pressed

I'm an amateur at PHP and MySQL so please could someone help, I would really appreciate it. Thank You.

php

3 Contributors
9 Replies
166 Views
23 Hours Discussion Span
Latest Post 11 Years Ago Latest Post by minitauros

TomH.PG 1 Newbie Poster

11 Years Ago

change the line to

$update = "UPDATE settings SET value = '$value' WHERE value = 'Login Script'";

but before you do that, you need to set $value first of course - it is not initialised in your code above. something like $value = $_POST['value']; etc

Edited 11 Years Ago by TomH.PG

BadManSam commented: Thanks! +1

minitauros 151 Junior Poster

11 Years Ago

This query:

SELECT value FROM settings WHERE value = "Login Script"

seems a bit odd to me. You are selecting the value field of records of which you already know what the value field contains (namely "Login Script"). It is something like saying:

SELECT "Login Script" AS value FROM settings WHERE value = "Login Script"

Now that must seem a bit odd, does it not? ;) If you want to get the value of value of a record of which you don't know the value of value yet, you should use another field to make a comparison. For example you could use

WHERE anotherfield = "Anothervalue", E.g. WHERE id = 5 or something like that.

As for your update query, I think you have misplaced your WHERE clause (but I'm not sure if it is not allowed to place it before your SET clause). I think it should be something like:

UPDATE settings SET value = "' . $new_value . '" WHERE field = yourvalue

An example:

<?php
// Changed it to include_once() as I think you will only need this file once per page load.
include_once('include/session.php');
$db = new PDO('mysql:host=localhost;dbname=******x2_login', '******x2_login', '********');

/***********************************************************************
Settings
*/

// Do we need to fetch data, or do we need to update data?
if($_POST)
{
    // We need to update data.
    $action = 'update';
}
else
{
    // We need to fetch data.
    $action = 'fetch';
}

switch($action)
{
    case 'fetch':

        // This query may return 1 - unlimited results, as we're not comparing it against anything.
        // I don't know which other columns reside in your table, so I'm not comparing it against anything
        // now.
        $query = 'SELECT value 
            FROM settings';

        $statement = $db->query($query);
        $results = $statement->fetchAll();

        if($results)
        {
            echo '<p>Your query returned the following results:</p><p>';

            foreach($results as $result)
            {
                echo 'Value: ' . $result['value'] . '<br>';
            }

            echo '</p>';
        }
        else
        {
            echo '<p>Your query did not return any results.</p>';
        }

        break;



    case 'update':

        // I see you are using PDO, which is good, so let's make use of preparing a statement.
        // Below is your query. You should rename "comparison_field" to the name of the field
        // you are using for comparison. That is, of course, only if you want to update one specific
        // field. If you wish to update ALL the fields in your "settings" table, you do not have
        // to specify this field. However, if you wish to update only one specific field, you
        // need to have some other field to find that field by.
        $query = 'UPDATE settings
            SET value = :value
            WHERE comparison_field = :key';

        // Prepare the statement.
        $statement = $db->prepare($query);

        // Bind parameters.
        $statement->bindParam(':value', $_POST['value'], PDO::PARAM_STR);
        $statement->bindParam(':comparison_field', $your_key, PDO::PARAM_STR); // If this is an integer, you should use PDO::PARAM_INT instead.

        // Execute the query.
        $success = $statement->execute();

        // Let's see if the query worked out.
        if($success)
        {
            echo '<p>Successfully updated ' . $success . 'rows!</p>';
        }
        else
        {
            echo '<p>Could not execute the query.</p>';
        }

        break;
}

/***********************************************************************
The HTML form
*/
?>

<html>
    <form action="" method="post">
        <table border="0" cellspacing="10">
            <tr>
                <td>
                    Name:
                </td> 
                <td>
                    <input type="text" name="value" size="100" value="">
                </td>
            </tr>
            <tr>
                <td>
                </td> 
                <td>
                    <INPUT TYPE="Submit" VALUE="Update the Record" NAME="Submit">
                </td>
            </tr>
        </table>
    </form>
</html>

Edited 11 Years Ago by minitauros

minitauros 151 Junior Poster

11 Years Ago

Well I guess it means that you have tried to initate too many connections. You should only construct your PDO object once. After it is constructed, you can work with that object the entire time (except if you need another connection, but I don't think you need that ;)?).

Maybe a better question: what is it that you are trying to make? :)

Edited 11 Years Ago by minitauros

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

BadManSam 0 Light Poster · Answer 1 · 2013-08-11T01:31:28+00:00

Hi, @TomH.PG thanks, it worked but now I can only change it once because it says

WHERE value = 'Login Script'

Could you help me with that please. So, it gets that row, no matter what the value of it is.

BadManSam 0 Light Poster · Answer 2 · 2013-08-11T18:57:01+00:00

Hi, I have updated my code but now when I click send, I get an error:

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /home/******x2/public_html/test.php on line 89

This is the new code:

<?php 
include_once("include/session.php"); 
?>
<?php
         $connectmysql = mysql_connect("localhost", "******x2_login", "********");

             mysql_select_db("******x2_login", $connectmysql);

              $query = mysql_query("SELECT value FROM settings WHERE id = '1' ", $connectmysql);

                 $num_rows = mysql_num_rows($query);
      ?>  

<?php
$db = new PDO('mysql:host=localhost;dbname=******x2_login', '******x2_login', '********');

$stmt = $db->query('SELECT value FROM settings WHERE value = 1 ');
$id = $stmt->fetchColumn(0);
if ($id !== false) {

} ?>

<?php
    // Do we need to fetch data, or do we need to update data?
if($_POST)
{
    // We need to update data.
    $action = 'update';
}
else
{
    // We need to fetch data.
    $action = 'fetch';
}

switch($action)
{
    case 'fetch':

        // This query may return 1 - unlimited results, as we're not comparing it against anything.
        // I don't know which other columns reside in your table, so I'm not comparing it against anything
        // now.
        $query = 'SELECT value 
            FROM settings WHERE id= 1 ';

        $statement = $db->query($query);
        $results = $statement->fetchAll();

        if($results)
        {
            echo '<p>Your query returned the following results:</p><p>';

            foreach($results as $result)
            {
                echo 'Value: ' . $result['value'] . '<br>';
            }

            echo '</p>';
        }
        else
        {
            echo '<p>Your query did not return any results.</p>';
        }

        break;



    case 'update':

        // I see you are using PDO, which is good, so let's make use of preparing a statement.
        // Below is your query. You should rename "comparison_field" to the name of the field
        // you are using for comparison. That is, of course, only if you want to update one specific
        // field. If you wish to update ALL the fields in your "settings" table, you do not have
        // to specify this field. However, if you wish to update only one specific field, you
        // need to have some other field to find that field by.
        $query = 'UPDATE settings
            SET value = :value
            WHERE id = 1';

        // Prepare the statement.
        $statement = $db->prepare($query);

        // Bind parameters.
        $statement->bindParam(':value', $_POST['value'], PDO::PARAM_STR);
        $statement->bindParam(':id', $your_key, PDO::PARAM_INT); // If this is an integer, you should use PDO::PARAM_INT instead.

        // Execute the query.
        $success = $statement->execute();

        // Let's see if the query worked out.
        if($success)
        {
            echo '<p>Successfully updated ' . $success . 'rows!</p>';
        }
        else
        {
            echo '<p>Could not execute the query.</p>';
        }

        break;
}

/***********************************************************************
The HTML form
*/
?>

<html>
    <form action="" method="post">
        <table border="0" cellspacing="10">
            <tr>
                <td>
                    Name:
                </td> 
                <td>
                    <input type="text" name="value" size="100" value="">
                </td>
            </tr>
            <tr>
                <td>
                </td> 
                <td>
                    <INPUT TYPE="Submit" VALUE="Save" NAME="submit">
                </td>
            </tr>
        </table>
    </form>
</html>

Now, I created a new column, called id, so now I have 3 columns, 'id, name, value'. But I don't get what do I replace $your_key with, what key. So now it displays whatever is in value with id 1. So, that's ok. But now why do I get this error? Please help.

minitauros 151 Junior Poster Featured Poster · Answer 3 · 2013-08-11T20:17:00+00:00

Oh yea I see I have a little mistake in my code. In PDO you bind parameters to values. For example if you have

WHERE field = :field AND field2 = :field2

you still need to tell PDO what :field and :field2 are, which you do using $statement->bindParam(). You are getting the error because you replaced :key by "1". You could use this code instead:

$query = 'UPDATE settings
    SET value = :value
    WHERE id = :id';

// Prepare the statement.
$statement = $db->prepare($query);


// Bind parameters.
$id = 1; // Change to what you want :id to be.

$statement->bindParam(':value', $_POST['value'], PDO::PARAM_STR);
$statement->bindParam(':id', $id, PDO::PARAM_INT); // If this is an integer, you should use PDO::PARAM_INT instead.

By the way, why are you using both the mysql_ functions AND the PDO class to connect to MySQL? :) I recommend using PDO only, as it is more secure.

BadManSam 0 Light Poster · Answer 4 · 2013-08-11T21:43:18+00:00

Hi, I've replaced it and now I get another error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [1040] Too many connections' in /home/promanx2/public_html/test.php:3 Stack trace: #0 /home/promanx2/public_html/test.php(3): PDO->__construct('mysql:host=loca...', '******x2_login', '********') #1 {main} thrown in /home/******x2/public_html/test.php on line 3

<?php
$db = new PDO('mysql:host=localhost;dbname=promanx2_login', 'promanx2_login', 'poiuy786');

$stmt = $db->query('SELECT value FROM settings WHERE id = 1 ');
$id = $stmt->fetchColumn(0);
if ($id !== false) {

} ?>

This is the start; line 3 is $db. What does the error mean? I have deleted the top mysql functions.

BadManSam 0 Light Poster · Answer 5 · 2013-08-11T22:51:43+00:00

@minitauros Thank You! For all the help. The error message was due to a problem with my web host. You have helped me a lot regarding this issue and I also learnt a couple of things, so THANKS!

minitauros 151 Junior Poster Featured Poster · Answer 6 · 2013-08-11T23:20:52+00:00

minitauros 151 Junior Poster

11 Years Ago

You're welcome :)!