INSERT query in PHP not working

Question

I am really new to PHP and am messing around with adding and editing database values. I have accomplished adding information to the database, but I cannot seem to figure out how to edit it properly!

I created a file called edit.php and here is the code I have thus far:

<?php

include '../database/connection.php';

$id = $_GET['id'];
$first_name = $_GET['first_name'];
$last_name = $_GET['last_name'];
$university_id = $_GET['university_id'];

$sql = "UPDATE master_roster (first_name, last_name, university_id) VALUES ('$first_name', '$last_name', '$university_id') WHERE id = $id";

?>

No error messages of any help are posting. Whenever the form is submitted and is handed off to this file, I just get a blank screen with no results. I cannot seem to figure out what it is I am missing to have it update the content from input fields!

EDIT: I gave all of suggestions a shot but it still does not work! Here is the form that the data is coming from:

<?php 
  $id = $_GET['id'];
  $first_name = $_GET['first_name'];
  $last_name = $_GET['last_name'];
  $university_id = $_GET['university_id'];

?>
<form action="edit_member.php?id=<?php echo $id; echo "&first_name="; echo $first_name; echo "&last_name="; echo $last_name; echo "&university_id="; echo $university_id; ?>" method="post">
  <table>
    <tr>
      <td>First Name</td>
      <td><input type="text" name="first_name" value="<?php echo $first_name; ?>"></td> 
    </tr>
    <tr>
      <td>Last Name</td>
      <td><input type="text" name="last_name" value="<?php echo $last_name; ?>"></td> 
    </tr>
    <tr>
      <td>University ID</td>
      <td><input type="text" name="university_id" value="<?php echo $university_id; ?>"></td>
    </tr>
    <tr>
      <td></td>
      <td><input type="submit" value="Submit"></td>
    </tr>
  </table>
</form>

I am not too concerned about SQL Injections at this point because I am just trying to learn the basics.

EDIT #2:

LIST.PHP - where the DB pulls all the members

<table>
  <tr>
    <th>ID</th>
    <th>First Name</th>
    <th>Last Name</th>
    <th>University ID</th>
  </tr>
  <?php
  include '../database/connection.php';
  $sql = "SELECT * FROM master_roster";
  if($result = mysqli_query($link, $sql)){
    if(mysqli_num_rows($result) > 0) {
      while($row = mysqli_fetch_array($result)){
        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . $row['first_name'] . "</td>";
        echo "<td>" . $row['last_name'] . "</td>";
        echo "<td>" . $row['university_id'] . "</td>";
        echo "<td><a href='form.php?id=" . $row['id'] . "&first_name=" . $row['first_name'] . "&last_name=" . $row['last_name'] . "&university_id=" . $row['university_id'] . "'>Edit</a></td>";
        echo "</tr>"; 
      }
      echo "</table>";
      mysqli_free_result($result); 
    } else {
      echo "No records matching your query were found."; 
    }
  } else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
  }
  mysqli_close($link);
  ?>

EDIT.PHP

<?php

include '../database/connection.php';

$id = mysqli_real_escape_string($_POST['id']);
$first_name = mysqli_real_escape_string($_POST['first_name']);
$last_name = mysqli_real_escape_string($_POST['last_name']);
$university_id = mysqli_real_escape_string($_POST['university_id']);

$sql = "UPDATE master_roster 
SET 
    first_name = '$first_name', 
    last_name = '$last_name', 
    university_id = '$university_id'
WHERE 
    id = $id";
?>

FORM.PHP

<form action="edit.php" method="post">
  <table>
    <tr>
      <td>First Name</td>
      <td><input type="text" name="first_name"></td> 
    </tr>
    <tr>
      <td>Last Name</td>
      <td><input type="text" name="last_name"></td> 
    </tr>
    <tr>
      <td>University ID</td>
      <td><input type="text" name="university_id"></td>
    </tr>
    <tr>
      <td><input type="submit" value="Submit"></td>
    </tr>
  </table>
</form>

*"INSERT query in PHP not working"* - yet you're using "UPDATE" which is a syntax error. I suggest you go back to the manuals. — Funk Forty Niner, Feb 25 '17 at 15:58
Apart from that: just defining a string is not enough to execute a query against a database server... — arkascha, Feb 25 '17 at 15:59
Here: https://dev.mysql.com/doc/refman/5.7/en/insert.html and https://dev.mysql.com/doc/refman/5.7/en/update.html being two different animals. — Funk Forty Niner, Feb 25 '17 at 16:00
and the api to connect with is unknown and the GET arrays as to where they're coming from. — Funk Forty Niner, Feb 25 '17 at 16:01
UPDATE works hand in hand with SET `UPDATE tablename SET columnname = newvalue WHERE condition` — oozmac, Feb 25 '17 at 16:01
*"No error messages of any help are posting."* - That's because you didn't execute the query and you're not checking for errors. Once you do execute it, you **will* get an error, provided you "check" for errors, both via error reporting and the query. — Funk Forty Niner, Feb 25 '17 at 16:15

score 4 · Answer 1 · edited Feb 25 '17 at 16:21

4

This is the wrong syntax for update. update takes a series of column=value clauses:

UPDATE master_roster 
SET    first_name = '$first_name', 
       last_name = '$last_name', 
       university_id = '$university_id'
WHERE  id = $id

Mandatory comment:
Using variable substitutions in strings like that leaves your code vulnerable to SQL injection attacks. You should consider using a prepared statement instead.

edited Feb 25 '17 at 16:21

Funk Forty Niner

74,450
15
68
141

answered Feb 25 '17 at 16:02

Mureinik

297,002
52
306
350

1

In all fairness to the other answer, [this comment](http://stackoverflow.com/questions/42458232/insert-query-in-php-not-working#comment72059456_42458330) and [this one](http://stackoverflow.com/questions/42458232/insert-query-in-php-not-working#comment72059498_42458330) also apply to this answer and should not be left out of the equation. – Funk Forty Niner Feb 25 '17 at 16:29
@Mureinik thanks for the help! I gave that a shot and put it inside $sql ="" but to no avail! – Fever Feb 25 '17 at 16:49

score 1 · Answer 2 · edited May 23 '17 at 12:25

1

You're writing wrong UPDATE statement.

Try this

$sql = "UPDATE master_roster 
SET 
    first_name = '$first_name', 
    last_name = '$last_name', 
    university_id = '$university_id'
WHERE 
    id = $id";

Now execute the query using statement below.

$result = $conn->query($sql);

$result will return true if the insertion is successful and false if not.

Use this to check if it is done or not.

if($result == false){
    die( "Connection Failed: ".$conn->error );
}

You should also be prevented from SQL injection. You can use mysqli_real_escape_string() method.

$first_name = $_GET['first_name']; $safe_first_name = mysqli_real_escape_string($conn, $first_name);

You can also use parameterized query.

Read this.

Now read the codes below modify your both pages like this.

Form

<form action="edit.php" method="post">
  <table>
    <tr>
      <td>First Name</td>
      <td><input type="text" name="first_name"></td> 
    </tr>
    <tr>
      <td>Last Name</td>
      <td><input type="text" name="last_name"></td> 
    </tr>
    <tr>
      <td>University ID</td>
      <td><input type="text" name="university_id"></td>
    </tr>
    <tr>
      <td><input type="submit" value="Submit"></td>
    </tr>
  </table>
</form>

This will update the data.

<?php

include '../database/connection.php';

$id = mysqli_real_escape_string($conn, $_POST['id']);
$first_name = mysqli_real_escape_string($conn, $_POST['first_name']);
$last_name = mysqli_real_escape_string($conn, $_POST['last_name']);
$university_id = mysqli_real_escape_string($conn, $_POST['university_id']);

$sql = "UPDATE master_roster 
SET 
    first_name = '$first_name', 
    last_name = '$last_name', 
    university_id = '$university_id'
WHERE 
    id = $id";

$result = $conn->query($sql);

if($result == false){
    die( "Connection Failed: ".$conn->error );
}

?>

edited May 23 '17 at 12:25

Community

1
1

answered Feb 25 '17 at 16:06

Siraj Alam

9,217
9
53
65

this query will fail due to most probable string literals not being quoted. It needs to be modified. – Funk Forty Niner Feb 25 '17 at 16:20
SQL injection has nothing to do with that question. – Siraj Alam Feb 25 '17 at 16:22
1

@chris85 ^ that's for you ;-) – Funk Forty Niner Feb 25 '17 at 16:22
1

*"SQL injection has nothing to do with that question"* - ok, but string literals do. – Funk Forty Niner Feb 25 '17 at 16:23
...but that's where you stand to be wrong. What if the username is `John O'Hare`? since the apostrophe is considered as an sql injection – Funk Forty Niner Feb 25 '17 at 16:24
1

Yeah right @Fred-ii-. Let me modify my answer one more time. – Siraj Alam Feb 25 '17 at 16:29
I noticed the edit. You realize that `mysql_` does not intermix with the mysqli_ api. – Funk Forty Niner Feb 25 '17 at 16:43
And make sure write your variable in place of `$conn`. – Siraj Alam Feb 25 '17 at 16:59
@Siraj Thanks again for those modifications! I attempted, once again but nothing is changing. – Fever Feb 25 '17 at 17:05
@Siraj I have literally copy/pasted everything you have posted but it still won't update. I went ahead and updated my original post to show ALL of my working files. I've been at this for hours but cannot seem to figure it out -- I appreciate your assistance! – Fever Feb 25 '17 at 17:14
@Siraj That's what the browser is throwing saying HTTP 500 Error, unable to handle this request. – Fever Feb 25 '17 at 17:24
This is not due to error in PHP. This is server error. – Siraj Alam Feb 25 '17 at 17:58
@Siraj the server handles PHP fine for all other apps. – Fever Feb 25 '17 at 18:05

INSERT query in PHP not working

2 Answers2