Updating data in database

[wikop]

Hi. I am new in PHP, i am new user in this forum. I've just registered because in forums in my language (polish) i hadn't found answer of my question. Anyway.

 

I want to make some kind of Admin Panel where i can change data in tables using POST.  But i don't know how to do button which will send to mysql an query about changing this data. I wan't to make it flexible - query changes when i add a table.

 

Can i do this or i must make that separately ?

 

Sorry for my bad english

 

 

$connection = mysql_connect("localhost", "root", "root")
or die('Brak po³¹czenia z serwerem MySQL');
$db = mysql_select_db("podreglami")
or die('Nie mogê po³¹czyæ siê z baz¹ danych');

$query = "SELECT tresc FROM `teksty`";
$result= mysql_query($query);
while ($row = mysql_fetch_array($result))
{
echo(' <textarea rows="10" cols="60" name="'	.$row['tytul']. '">'	.$row['tresc'].'	</textarea><br /><br />');
}

[Mchl]

Perhaps you should start with something that is not 'flexible' until you learn basics of form handling and database operations.

 

Try here:

http://www.phpfreaks.com/tutorial/php-basic-database-handling

[awjudd]

For starters, you are going to have to throw all of your text areas into a <form> tag.  This will tell the web browser where to send the data once the button has been pressed.

 

After that, you will need to add the actual submit button and then process the information which is sent to the processing script.

 

[php
$connection = mysql_connect("localhost", "root", "root")
or die('Brak po³¹czenia z serwerem MySQL');
$db = mysql_select_db("podreglami")
or die('Nie mogê po³¹czyæ siê z baz¹ danych');

$query = "SELECT tresc FROM `teksty`";
$result= mysql_query($query);
       
        echo "<form method = 'post' action = ''>"; // Tell the form to send data through $_POST and then send it back to the current page

while ($row = mysql_fetch_array($result))
{
echo(' <textarea rows="10" cols="60" name="' .$row['tytul']. '">' .$row['tresc'].' </textarea><br /><br />');
}

        echo "<input type = 'submit' value = 'Save Settings' />"; // Add the 'sending' button

        echo "</form>";
[/code]

 

Then for the processing part, which would probably end up being placed above the previous code, you could do something like this.

 

     foreach ( $_POST as $key => $value ) // Loop through all of the posted values (and their associated keys (keys are the 'name' field from the HTML) )
     {
            // TODO: Add some verification to make sure that the field specified exists
            $value = msyql_real_escape_string ( $value ); // Sanitize the input (more should probably be done)

            $query = "UPDATE `teksty` SET `" . $key . "` = '" . $value . "'"; // Generate the query
            mysql_query ( $query ); // Update the table
     }

 

Something along those lines should work I believe.

 

~juddster

[wikop]

i screwed something:

 

if (isset($_GET['odomku']))
{
mysql_query("UPDATE `podreglami`.`teksty` SET `tresc` = \'"$_GET['odomku']"\' WHERE `teksty`.`tytul` = odomku;");
}

 

unexpected T_variable

[Mchl]

$tresc = mysql_real_escape_string($_GET['odomku']); //this is to avoid SQL injection
mysql_query("UPDATE podreglami.teksty SET tresc = '$tresc' WHERE tytul = 'odomku';");

[kenrbnsn]

You're escaping the wrong quotes. Also if you want to use array references in a quoted string you need to enclose them in curly braces "{ }"

<?php
mysql_query("UPDATE `podreglami`.`teksty` SET `tresc` = '{$_GET['odomku']}' WHERE `teksty`.`tytul` = odomku");
?>

 

You're inviting trouble when you use value directly from the URL or a form in a MySQL query. Always use the function mysql_real_escape_string on character values.

<?php
mysql_query("UPDATE `podreglami`.`teksty` SET `tresc` = '" . mysql_real_escape_string($_GET['odomku']) . "' WHERE `teksty`.`tytul` = odomku");
?>

 

Ken

[wikop]

does not work

 

<?php
ob_start();

if (isset($_GET['odomku']))
{
mysql_query("UPDATE `podreglami`.`teksty` SET `tresc` = '" . mysql_real_escape_string($_GET['odomku']) . "' WHERE `teksty`.`tytul` = 'odomku'");
}


?>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link href="style.css" rel="stylesheet" type="text/css" />
</head>

<body>
<div class="content">
<div class="okno">
<form action="<?php echo $_SERWER["PHP_SELF"];?>" method="post">
<?php
$connection = mysql_connect("localhost", "root", "root")
or die('Brak po³¹czenia z serwerem MySQL');
$db = mysql_select_db("podreglami")
or die('Nie mogê po³¹czyæ siê z baz¹ danych');
$od_sql = "SELECT tresc FROM `teksty` WHERE tytul=\"odomku\"";
$od_result= mysql_query($od_sql);
$od_row = mysql_fetch_array($od_result);
?>
<textarea name="odomku" rows="10" cols="50"><?php echo($od_row['tresc']); ?></textarea>
<input type="submit" value="Zapisz">
</form> 


    </div>
</div>
</body>
<?php ob_end_flush(); ?>
</html>

[kenrbnsn]

How doesn't it work? Errors?

 

Ken

[wikop]

There's no changes in database.

 

localhost

root
root

db - podreglami

table - teksty

id | tytul (title) | tresc (text)

[Mchl]

It doesn't work, because you put this code before you establish connection with your database

[wikop]

still doesn't work

 

<?php
ob_start();
$connection = mysql_connect("localhost", "root", "root")
or die('Brak po³¹czenia z serwerem MySQL');
$db = mysql_select_db("podreglami")
or die('Nie mogê po³¹czyæ siê z baz¹ danych');
$od_sql = "SELECT tresc FROM `teksty` WHERE tytul=\"odomku\"";
$od_result= mysql_query($od_sql);
$od_row = mysql_fetch_array($od_result);

if (isset($_GET['odomku']))
{
mysql_query("UPDATE `podreglami`.`teksty` SET `tresc` = '" . mysql_real_escape_string($_GET['odomku']) . "' WHERE `teksty`.`tytul` = 'odomku'");
}


?>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link href="style.css" rel="stylesheet" type="text/css" />
</head>

<body>
<div class="content">
<div class="okno">
<form action="<?php echo $_SERWER["PHP_SELF"];?>" method="post">
<?php

?>
<textarea name="odomku" rows="10" cols="50"><?php echo($od_row['tresc']); ?></textarea>
<input type="submit" value="Zapisz">
</form> 


    </div>
</div>
</body>
<?php ob_end_flush(); ?>
</html>

[Mchl]

Please read and apply: http://www.phpfreaks.com/tutorial/debugging-a-beginners-guide/page1

 

BTW: It's $_SERVER not $_SERWER

[awjudd]

You are using $_GET to check against, when all of the information is being posted ($_POST).

 

Please refer to my first post.

 

~juddster

[wikop]

It works only, when i change action of form to get. Then all informations is in adress bar. I don't really know this time, what can i do, to change bad things to POST, and make it working.

 

When action is GET - i must refresh once to get real data from base in textarea.

 

I don't know...

[Mchl]

Did you try changing this line:

if (isset($_GET['odomku']))

to

if (isset($_POST['odomku']))

 

when you changed form action to post?

[awjudd]

It works only, when i change action of form to get. Then all informations is in adress bar. I don't really know this time, what can i do, to change bad things to POST, and make it working.

 

Yet again ... my first post here contains code which would be very useful for the conversion to the $_POST

 

     foreach ( $_POST as $key => $value ) // Loop through all of the posted values (and their associated keys (keys are the 'name' field from the HTML) )
     {
            // TODO: Add some verification to make sure that the field specified exists
            $value = msyql_real_escape_string ( $value ); // Sanitize the input (more should probably be done)

            $query = "UPDATE `teksty` SET `" . $key . "` = '" . $value . "'"; // Generate the query
            mysql_query ( $query ); // Update the table
     }

 

When action is GET - i must refresh once to get real data from base in textarea.

 

I don't know...

 

Your script does the following:

i) Grabs the data

ii) Updates the database

iii) Displays the data from step 1

 

This will make it so that you will never get the updated values until you actually refresh.  Move the update up, or re-do the SELECT query within the block of code where it is being updated and it will work as expected.

 

~juddster

[wikop]

Yes. I still must refresh to see real data.

 

Sorry.

[awjudd]

Have you added the second SELECT that I previously mentioned? ... or moving the initial select to after where you have the UPDATE in your code?

 

~juddster