MySQL query is failing

Question

I'm getting the following error because my MySQL query is failing, but I have tried and tried and cannot fix this.

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in F:\xampp\htdocs\LMCS Incident Manager\New\edit.php on line 40 Error: Data not found..

PHP code:

$con = mysql_connect("localhost", "root", "");

if (!$con) 
{
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("cad", $con);

$query = "SELECT id FROM cad";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)) 
{
    $row['id'];
} 

$id = $row['id'];

$result = mysql_query("SELECT * FROM `cad` WHERE `id` = $id");
$row = mysql_fetch_array($result);

if (!$result) 
{
    die("Error: Data not found..");
}

I think the problem is that my second query,

$result = mysql_query("SELECT * FROM `cad` WHERE `id` = $id");

is failing and it stops. I think though it's not able to get the "id" of the table row that I'm trying to retrieve through my MySQL database but I'm not sure.

Table.php code:

<!-- Table -->

<form action="index.php" method="get" id="dispatch">



         <table>
             <thead>
             <tr>
                 <th>Incident #</th>
                 <th>Town</th>
                 <th>Location</th>
                 <th>Incident Type</th>
                 <th>Time/Date</th>
                 <th>Admin</th>
                 <th>Edit Entry</th>
            </tr>
             </thead>
             <tbody>
             <?php


  if( isset($_POST['town']) )
  {
    $town = $_POST['town'];
  }

  if( isset($_POST['location']) )
  {
  $location = $_POST['location'];
  }

  if( isset($_POST['incident_type']) )
  {
  $incident_type= $_POST['incident_type'];
  }


  if( isset($_POST['time_date']) )
  {
  $time_date= $_POST['time_date'];
  }

  if( isset($_POST['admin']) )
  {
  $admin = $_POST['admin'];
  }

  if( isset($_POST['id']) )
  {
  $id = $_POST['id'];
  }



    $db = mysqli_connect('localhost','root','') or die("Database error"); 
    mysqli_select_db($db, 'cad');  
    $result= mysqli_query($db, "SELECT * FROM `cad` ORDER BY `time_date` DESC LIMIT 20"); 


  while($row = mysqli_fetch_array($result))
    {

  $town     = $row['town'];
  $location    = $row['location'];
$incident_type = $row['incident_type']; 
  $time_date = $row['time_date'];
  $admin    = $row['admin']; 
  $id    = $row['id'];                  



       /*           


      date_default_timezone_set('America/Chicago');

      $timestamp = strtotime(date("Y-m-d H:i:s")) + 3600;

                $time = date('Y-m-d H:i:s', $timestamp);





                if ($time_date >= $timestamp)
                    echo "<tr class=\"tr-black\">";
                else 
                    echo "<tr class=\"tr-red\> */



                echo "<tr>

                        <td class=\"id-center\">
                            ".$id."
                        </td>
                        <td >
                            ".$town."
                        </td>
                        <td>
                            ".$location."
                        </td>
                       <td>
                            ".$incident_type."
                        </td>

                        <td>
                            ".$time_date."
                        </td>
                        <td >
                            ".$admin."
                        </td>

                        <td>
                        <a id=\"edit-left\" href=\"edit.php?id=$id\" onclick=\"return confirm('Are you sure you want to edit this incident?');\" name=\"edit\" value=\"$id\" class=\"btn btn-primary btn-default center-1\"><span class=\"glyphicon glyphicon-edit\"></span></a>




                        <a id=\"edit-right\" href=\"delete.php?id=$id\" onclick=\"return confirm('Are you sure you want to delete this incident?');\" name=\"delete\" value=\"$id\" class=\"btn btn-primary btn-default center-1\"><span class=\"glyphicon glyphicon-trash\"></span></a>
                        </td> 


                        </tr>";
    }

  mysqli_close($db);


  ?>

             </tbody>
             </table> 
             </form>

<!-- End -->

Explanation: This is my table in my table.php file: codebin.org/view/ca0998b1. This is what displays the info. From here, I want to edit a table row when a person clicks on a button in the table that I have made. Once they do that then it brings them to my edit.php file: codebin.org/view/b30b7138. Basically my problem in the edit.php file is that I need to get that certain table row from table.php to edit it from my database.

$row[ID] exist only in while loop and no after. Your artichtecture is not correct, i think so you need rethink really what you want to do. — daremachine, Jul 03 '15 at 00:18
You've added some kind of error handling for the first call to mysql_query(). But you didn't for the second ...even though you expect it to be the source of the problem. Why? | And what are you trying to achieve (not how, what). It looks like you select all records just to store the last one and make another query out of it. — VolkerK, Jul 03 '15 at 00:37
Please put the table code here in the question, not a link to codebin. — Barmar, Jul 03 '15 at 01:19

score 1 · Answer 1 · answered Jul 03 '15 at 01:18

The problem is this code:

while($row = mysql_fetch_array($result)) 
{
    $row['id'];
} 
$id = $row['id'];

Inside the while loop you're not doing anything with $row['id']. The loop ends when mysql_fetch_array() returns false, and on this last iteration it assigns that to $row. So after the loop is done, you're doing:

$id = false['id'];

This is meaningless, and sets $id to NULL. Then when you try to do the second query, it's doing:

$result = mysql_query("SELECT * FROM `cad` WHERE `id` = ");

This is invalid SQL, so you get an error.

See @Volkerk's answer for how to code everything correctly.

score 0 · Answer 2 · answered Jul 03 '15 at 00:18

0

This:

$id = $row['id'];

Will never work, because your $row variable is only available within your while() loop. What you want to do is this:

while($row=mysql_fetch_array($result)) {
    $id = $row['id'];
}

But I don't understand why you're doing this... You're fetching the id's and then trying to fetch the data....? Why not fetch everything in one query...?

answered Jul 03 '15 at 00:18

Darren

12,924
4
37
76

I'm trying to get the id of the row so I can fetch the whole row. I'm doing this because this is to edit a table row. So I need to get the table row and then edit it. – huth2349 Jul 03 '15 at 00:23

score 0 · Answer 3 · answered Jul 03 '15 at 00:18

0

The issue is this line:

$row = mysql_fetch_array($result);

http://php.net/manual/en/function.mysql-fetch-array.php

You aren't passing it the correct parameters, you're only giving it a boolean

answered Jul 03 '15 at 00:18

isuPatches

5,354
2
19
29

Yes, I know that, my question was asking how do I fix this though. – huth2349 Jul 03 '15 at 02:38

score 0 · Answer 4 · answered Jul 03 '15 at 00:53

0

The only way (I can think of) your script makes some sense is that you want to select only the "last" record.
If so, you might be interested in ORDER BY and LIMIT

<?php
// you really should change the default settings, root with no password isn't such a good idea
$con = mysql_connect("localhost","root",""); 
if (!$con) {
    // crude error handling
    // you might want to improve it
    // see https://www.owasp.org/index.php/Information_Leakage
    die('Could not connect: ' . mysql_error()); 
}

mysql_select_db("cad", $con)
    or die('Could not select database'); // again crude, but anyway


// SELECT only the record with the highest id
// SELECT * is frowned upon - it's working, yet ... just write out the field names...
$query='
    SELECT
        id, foo, bar, baz
    FROM
        cad
    ORDER BY
        id DESC
    LIMIT
        1
';

$result=mysql_query($query)
  or die(mysql_error()); //crude

while( false!=($row=mysql_fetch_array($result)) )
{
    echo '<td>', htmlspecialchars($row['bar']), '</td>';
}

answered Jul 03 '15 at 00:53

VolkerK

93,904
19
160
225

btw: The mysql_* extension is deprecated. Pick another API. see http://docs.php.net/manual/en/mysqlinfo.api.choosing.php – VolkerK Jul 03 '15 at 00:57
I actually want to get a table row's id from the database in order to get it's row in the database and then edit it. Do you think you could edit your answer to reflect this? – huth2349 Jul 03 '15 at 01:03
Not really, because it still doesn't make sense. What criterion determines which row you want to edit? – VolkerK Jul 03 '15 at 01:05
This is my table in my table.php file: http://codebin.org/view/ca0998b1. This is what displays the info. From here, I want to edit a table row when a person clicks on a button in the table that I have made. Once they do that then it brings them to my edit.php file: http://codebin.org/view/b30b7138. Basically my problem in the edit.php file is that I need to get that certain table row from table.php to edit it from my database. I hope you understand what I'm trying to do now. – huth2349 Jul 03 '15 at 01:17
1

You should get the ID from a form parameter, not by looping through the table. – Barmar Jul 03 '15 at 01:18
Yes, as Barmar says. `href=\"edit.php?id=$id\"` – VolkerK Jul 03 '15 at 01:19

MySQL query is failing

4 Answers4