How can I update MySQL with PHP, using the following syntax:

Question

<?php
//connect to server

$connect = mysql_connect("localhost","name","password");

//connect to db

mysql_select_db("complexm_pondlife", $connect);


//query the db

$query = mysql_query("SELECT * FROM frogs");

error_reporting(E_ALL);
ini_set('display_errors', 1);


?>

<button onclick="show()">SHOW DATA</button>

<p id="clip"style="visibility: hidden">



<?php
WHILE($rows = mysql_fetch_array($query)):

$name = $rows['name'];
$age = $rows['age'];
$sound = $rows['sound'];
$id = $rows['id'];


?>
<?php
echo $id.") "."Name: ";
?>
<form action = "" method = "post">

    <input type="text" id="name" value='<?=$name?>'>

    <input type="submit" name="update_db" value="Update">

</form>
<?php
echo "Age: "."$age<br><br>";
echo "Sound: "."$sound<br><br>";
echo "___________<br><br>";


endwhile;

?>


</p>

<?php

function upload(){

mysql_query("UPDATE frogs SET name = '$name' WHERE name = '$name'");

}

if(isset($_POST['update_db'])){

    echo upload();

}

?>

<script>
function show(){
  document.getElementById('clip').style.visibility="visible";
}
</script>

This code gives me: Notice: Undefined variable: name in /home1/complexm/public_html/projects.php on line 70

I dont know why though. So if anyone can tell me i would like to know. If the syntax is wrong please tell me!

http://dev.mysql.com/doc/refman/5.0/en/update.html <= start there. — Funk Forty Niner, Nov 10 '14 at 19:43
@CarlosCarlsen - ***why*** doesn't it work for you? What exactly doesn't work? http://stackoverflow.com/help/how-to-ask — LittleBobbyTables - Au Revoir, Nov 10 '14 at 19:56
You're using a JS-based function syntax and trying to call a PHP-based function, that's one reason why it's not firing up. — Funk Forty Niner, Nov 10 '14 at 19:57
Plus this `UPLOAD frogs SET name = "$name" WHERE name="TreeFrog";` you're missing quite a few things in there. Do Google "mysql update". Chances are, you'll end up back on Stack with q's and a's. — Funk Forty Niner, Nov 10 '14 at 19:58
It is helpful when you give us all the parts. We have no idea what API you're using to connect with. You're probably mixing MySQL APIs, hard hard hard to say. Here, add error reporting to the top of your file(s) right after your opening ` — Funk Forty Niner, Nov 10 '14 at 20:04
`function upload(){ mysql_query("UPDATE frogs SET name = '$name' WHERE name = 'TreeFrog'"); }` there, valid. Please read up on doing queries and properly. — Funk Forty Niner, Nov 10 '14 at 20:06
You're well on your way to success now. A word of advice though: Your present code is open to [**SQL injection**](http://stackoverflow.com/q/60174/). Use [**`mysqli` with prepared statements**](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), or [**PDO with prepared statements**](http://php.net/pdo.prepared-statements). — Funk Forty Niner, Nov 10 '14 at 20:12

score 3 · Accepted Answer · edited May 23 '17 at 10:26

This answer is based on your original post and not marking it as an edit, should anyone wonder.

The reason why your upload() function is failing, is because you haven't included the mysql_query() function, along with a few missing parts. (Parts, being quotes/brackets).

function upload(){ 

mysql_query("UPDATE frogs SET name = '$name' WHERE name = 'TreeFrog'");

}

A word of advice though:

Your present code is open to SQL injection.
Use mysqli with prepared statements, or PDO with prepared statements, they're much safer.

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Sidenote: Error reporting should only be done in staging, and never production.

also or die(mysql_error()) to mysql_query().

Edit:

To fire up your function using a PHP method, I recommend you change the
<input type="submit" onclick="update()"> to
<input type="submit" name="update_db" value="Update"> and wrapping an isset() around it.

I.e.:

<?php

function upload(){ 

mysql_query("UPDATE frogs SET name = '$name' WHERE name = 'TreeFrog'");

}

if(isset($_POST['update_db'])){

    echo upload();

}

?>

However, you will need <form></form> tags around your form's element(s) and a post method.

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

    <input type="text" id="name" value='<?=$name?>'>

    <input type="submit" name="update_db" value="Update">

</form>

Edit #2:

This is a mysqli_ method, please change the DB credentials to match yours if they do not match. I had to remove the upload() function, it was giving me too much trouble.

A hidden input has been added in the form, which is essential to doing updates like these.

<?php

error_reporting(E_ALL);
ini_set('display_errors', 1);

//connect to server
$DB_HOST = 'localhost';
$DB_USER = 'name';
$DB_PASS = 'password';
$DB_NAME = 'complexm_pondlife';

$link = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($link->connect_errno > 0) {
  die('Connection failed [' . $link->connect_error . ']');
}

//query the db
$query = mysqli_query($link,"SELECT * FROM frogs");
?>

<button onclick="show()">SHOW DATA</button>

<p id="clip"style="visibility: hidden">

<?php
WHILE($rows = mysqli_fetch_array($query)):

 $name = $rows['name'];
 $age = $rows['age'];
 $sound = $rows['sound'];
 $id = $rows['id'];

?>
<?php
 echo $id.") "."Name: ";
?>

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

    <input type="text" id="name" name="thename" value="<?php echo $name; ?>">
    <input type="hidden" name="the_id" value="<?php echo $id; ?>">
    <input type="submit" name="update_db" value="Update">

<br>

</form>
<?php
 echo "Age: "."$age<br><br>";
 echo "Sound: "."$sound<br><br>";
 echo "___________<br><br>";

endwhile;

?>

</p>

<?php

if(isset($_POST['update_db'])){

$theid = stripslashes($_POST['the_id']);
$theid = mysqli_real_escape_string($link,$_POST['the_id']);

$thename = stripslashes($_POST['thename']);
$thename = mysqli_real_escape_string($link,$_POST['thename']);

$results=  mysqli_query($link, "UPDATE frogs SET name = '$thename' WHERE id = '$theid'");
}

?>

<script>
function show(){
  document.getElementById('clip').style.visibility="visible";
}
</script>

You can also redirect to the same page by adding this at the top:

<?php 
ob_start();
?>

then adding this after your query:

if($results){
  header("Location: http://www.yoursite.com/update_frogs.php");
}

How can I update MySQL with PHP, using the following syntax:

1 Answers1