Fetching records from database and display empty records once deleted in php mysql

Question

Hi iam trying to fetch all the records from database but it is displaying only one record if there are more than 5 records also.I have tried with executing query in database it is working correctly can anyone help me regarding this. If i delete the records and if there is no records in database it is displaying the delete option as well as the image option in front end. Here is my code.

image.php

<?php
    $connection = mysql_connect("localhost", "root", "") or die(mysql_error());
    $db = mysql_select_db("accountant", $connection);
    $res = "SELECT *  FROM blogs ";
    $result=mysql_query($res);
    $row = mysql_fetch_array($result);
?>

blogimage.php

<form method="post" action="image.php" id="myform">
    <table>
        <thead>
            <tr>
                <th scope="col">Title</th>
                <th scope="col">Image</th>
                <th scope="col" style="width: 65px;">Modify</th>
            </tr>
        </thead>
        <tbody>
        <?php include "image.php" ;?>   
            <tr>
                <td><?php echo $row['blog_title'];?></td>
                <td><img src="upload/<?php echo $row['image'];?>" height="100" width="100"/></td>
                <td><a class="buttons delete" href="deleteblog.php" onclick="return confirm('Are you sure to delete');" class="table-icon delete" >Delete Blog</a></td>
            </tr>
        </tbody>
    </table>
</form>

deleteblog.php

$id=$_GET['blog_id'];
$res = "DELETE 
FROM blogs 
WHERE blog_id=$id";
if($res)
{
echo "successfully deleted";
}
else{
echo "Failure";
}

Rakesh Sojitra · Answer 1 · 2016-02-06T05:17:22.520

Change your php code from

<?php
    $connection = mysql_connect("localhost", "root", "") or die(mysql_error());
    $db = mysql_select_db("accountant", $connection);
    $res = "SELECT *  FROM blogs ";
    $result=mysql_query($res);
    $row = mysql_fetch_array($result);
?>

to this

<?php
$connection = mysql_connect("localhost", "root", "") or die(mysql_error());
$db = mysql_select_db("accountant", $connection);
$res = "SELECT *  FROM blogs ";
$result = mysql_query($res);
while($row = mysql_fetch_array($result)){
$data[] = $row;
}
?>

and html/php code from this

<form method="post" action="image.php" id="myform">
    <table>
        <thead>
            <tr>
                <th scope="col">Title</th>
                <th scope="col">Image</th>
                <th scope="col" style="width: 65px;">Modify</th>
            </tr>
        </thead>
        <tbody>
        <?php include "image.php" ;?>   
            <tr>
                <td><?php echo $row['blog_title'];?></td>
                <td><img src="upload/<?php echo $row['image'];?>" height="100" width="100"/></td>
                <td><a class="buttons delete" href="deleteblog.php" onclick="return confirm('Are you sure to delete');" class="table-icon delete" >Delete Blog</a></td>
            </tr>
        </tbody>
    </table>
</form>

to this

<form method="post" action="image.php" id="myform">
    <table>
        <thead>
<?php if(!empty($data)){ ?>
            <tr>
                <th scope="col">Title</th>
                <th scope="col">Image</th>
                <th scope="col" style="width: 65px;">Modify</th>
            </tr>
        </thead>
        <tbody>
<?php include "image.php"; ?>   
<?php foreach ($data as $row) { ?>
            <tr>
                <td><?php echo $row['blog_title']; ?></td>
                <td><img src="upload/<?php echo $row['image']; ?>" height="100" width="100"/></td>
                <td><a class="buttons delete" href="deleteblog.php" onclick="return confirm('Are you sure to delete');" class="table-icon delete" >Delete Blog</a></td>
            </tr>
<?php } ?>
<?php } ?>
        </tbody>
    </table>
</form>

It is not working if there records then it is not showing one record also it is showing blank page — Nagu, Feb 06 '16 at 05:11
If there is no record in database then it will not show any data. what you want if there is no record in db ? — Rakesh Sojitra, Feb 06 '16 at 05:22
it is working fine if there are no records but it should display the data if the records are there in db right but it is not working for that — Nagu, Feb 06 '16 at 05:36
Its working perfectly at myside. I have executed code. may be problem is in your image.php file. — Rakesh Sojitra, Feb 06 '16 at 05:36

score 0 · Accepted Answer · edited May 23 '17 at 11:50

0

Look at this statement here,

$row = mysql_fetch_array($result);

You're fetching only one row from the result set.

Solution:

First of all, delete this line, $row = mysql_fetch_array($result); from your image.php page and then on blogimage.php page, loop through the result set to display all data, like this:

// your code

<?php 

    include "image.php";

    while($row = mysql_fetch_array($result)){
        ?>
        <tr>
            <td><?php echo $row['blog_title']; ?></td>
            <td><img src="upload/<?php echo $row['image']; ?>" height="100" width="100"/></td>
            <td><a class="buttons delete" href="deleteblog.php" onclick="return confirm('Are you sure to delete');" class="table-icon delete" >Delete Blog</a></td>
        </tr>
        <?php
    }
?>

// your code

Sidenote: Don't use mysql_* functions, they are deprecated as of PHP 5.5 and are removed altogether in PHP 7.0. Use mysqli or pdo instead. And this is why you shouldn't use mysql_* functions.

Edited:

This is how you should perform DELETE operation.

<?php

    $connection = mysql_connect("localhost", "root", "") or die(mysql_error());
    $db = mysql_select_db("accountant", $connection);

    $id=$_GET['blog_id'];
    $res = "DELETE FROM blogs WHERE blog_id=$id";

    // Execute the query
    mysql_query($res);

    if(mysql_affected_rows()){
        echo "successfully deleted";
    }else{
        echo "Failure";
    }

?>

edited May 23 '17 at 11:50

Community

1
1

answered Feb 06 '16 at 05:13

Rajdeep Paul

16,887
3
18
37

How can i pass id to delete record – Nagu Feb 06 '16 at 05:33
@Nagu Append the image id to your `href` attribute value, like this: `... href="deleteblog.php?id="`. And on **deleteblog.php** page get the id using `$id = $_GET['id'];` and delete that particular image. – Rajdeep Paul Feb 06 '16 at 05:39
As i given..... Delete Blog.... Here blog_id is my db table id and deleteblog.php i has given as ....$id=$_GET['blog_id'];.... But while executing it is getting error as undefined index blog_id and displaying message as successfully deleted but it is not deleting – Nagu Feb 06 '16 at 05:55
@Nagu You're doing it wrong, it should be, `Delete Blog` and on **deleteblog.php** page, catch it using `$id=$_GET['blog_id'];` – Rajdeep Paul Feb 06 '16 at 05:58
It is showing as successfully deleted but not deleting the record – Nagu Feb 06 '16 at 06:02
@Nagu Did you correct the `href` attribute like I [suggested](http://stackoverflow.com/questions/35237534/fetching-records-from-database-and-display-empty-records-once-deleted-in-php-mys/35237607#comment58190078_35237607)? I don't know what's there in **deleteblog.php** page, what query are you using to delete the record? – Rajdeep Paul Feb 06 '16 at 06:05
Ya i have changed my href link to this Delete Blog And my deleteblog.php has been updated above. – Nagu Feb 06 '16 at 06:08
@Nagu You didn't execute the query. And use [`mysql_affected_rows()`](http://php.net/manual/en/function.mysql-affected-rows.php) to check how many rows are affected by the delete query. – Rajdeep Paul Feb 06 '16 at 06:13
Iam not getting what you are saying – Nagu Feb 06 '16 at 06:15
@Nagu Glad I could help. :-) – Rajdeep Paul Feb 06 '16 at 06:26
i need a small favour last one how can i give hyperlink to bolg_title....... if iam trying to to add – Nagu Feb 06 '16 at 06:35
As it should redirect to another to another page where i can show image as well as description – Nagu Feb 06 '16 at 06:36
Do this, `...` – Rajdeep Paul Feb 06 '16 at 06:37
title is displaying but hyperlink is not working if i click on that title it should redirect to another page where i can show that image and description in different format – Nagu Feb 06 '16 at 06:41
@Nagu Oh, my mistake, it should be, `...` – Rajdeep Paul Feb 06 '16 at 06:47
As i gave like this..... becuase when i click on the title it should redirect to the page ....http://localhost/accounting/blogimages.php/Sample1 – Nagu Feb 06 '16 at 06:53
As i have created blogimages.php page but in the url it should print the title name also how can we do that – Nagu Feb 06 '16 at 06:54
@Nagu That you can only do that using *.htaccess* file. Refer [this SO question](http://stackoverflow.com/questions/13680295/how-to-rewrite-url-in-php-using-htaccess-file-what-change-in-php-code). – Rajdeep Paul Feb 06 '16 at 06:57
Iam not understanding how they have written that – Nagu Feb 06 '16 at 07:18
@Nagu You need to learn how to write *htaccess* configuration file. Google it and learn. SO will help you if you get stuck at any point. – Rajdeep Paul Feb 06 '16 at 07:22
i have done like by comparing title names i should display the data for that purpose i have used in html ..........Here iam able to get the title name but in query if i use $id=$_GET['title'] and in where condition i have compared where blog_title=$id getting an error as Notice: Undefined index: title,Warning: mysql_fetch_array() expects parameter 1 to be resource, – Nagu Feb 06 '16 at 09:11
@Nagu I'm not sure what exactly you're trying to do. Form a separate question and ask. – Rajdeep Paul Feb 06 '16 at 09:58

A J · Answer 3 · 2016-02-06T05:34:59.817

Do not use mysql_* functions as these are deprecated. Instead use `mysqli_*.

And here is the code that you should try

image.php

<?php
$connection = mysqli_connect("localhost", "root", "", "accountant") or die(mysqli_error());
$res = "SELECT *  FROM blogs ";
$result= $connection->query($res);
?>

Your code on blogimage.php should go like this.

<table>
        <thead>
            <tr>
                <th scope="col">Title</th>
                <th scope="col">Image</th>
                <th scope="col" style="width: 65px;">Modify</th>
            </tr>
        </thead>
        <tbody>
        <?php include "image.php" ;
            while($row =  $connection->fetch_assoc($result))
            {
                ?>
                <tr>
                    <td><?php echo $row['blog_title'];?></td>
                    <td><img src="upload/<?php echo $row['image'];?>" height="100" width="100"/></td>
                    <td><a class="buttons delete" href="deleteblog.php?id=<?php echo $row['id']; ?>" onclick="return confirm('Are you sure to delete');" class="table-icon delete" >Delete Blog</a></td>
                </tr>
                <?php
            }
        ?>   

        </tbody>
        </table>

EDIT

To delete a record, pass the id in the URL like in the code deleteblog.php?id=<?php echo $row['id']; ?> and then use the following code.

deleteblog.php

<?php
    $connection = mysqli_connect("localhost", "root", "", "accountant") or die(mysqli_error());
    $deletequery = " DELETE FROM blogs WHERE id=".intval($_GET['id']);//your delete query
    $result= $connection->query($res);//execute query
    ?>

It is working but if i delete the record it is showing that it is successfully deleted but it is not deleting the record before it is deleting. — Nagu, Feb 06 '16 at 05:19

Vadivel S · Answer 4 · 2016-02-06T06:36:06.543

Image.php

<?php
$connection = mysql_connect("localhost", "root", "") or die(mysql_error());
$db = mysql_select_db("accountant", $connection);
$res = "SELECT *  FROM blogs ";
$result=mysql_query($res);
$row = mysql_fetch_array($result);
?>

Blogimage.php

<form method="post" action="image.php" id="myform">
    <table>
        <thead>
            <tr>
                <th scope="col">Title</th>
                <th scope="col">Image</th>
                <th scope="col" style="width: 65px;">Modify</th>
            </tr>
        </thead>
        <tbody>
        <?php include "image.php"
 while($row =  $connection->fetch_assoc($result))
            {
;?>   
            <tr>
                <td><?php echo $row['blog_title'];?></td>
                <td><img src="upload/<?php echo $row['image'];?>" height="100" width="100"/></td>
                <td><a  class="buttons delete" href="deleteblog.php?blog_id=<?php echo $row['blog_id']; ?>" onclick="return confirm('Are you sure to delete');" class="table-icon delete" >Delete Blog</a></td>
            </tr>
          <?php } ?>
        </tbody>
    </table>
</form>

deleteblog.php

<?php
$id=$_GET['blog_id'];
$connection = mysql_connect("localhost", "root", "") or die(mysql_error());
$db = mysql_select_db("accountant", $connection);
$res = "delete  FROM blogs where blog_id=$id ";
$result=mysql_query($res);
header('Location: blogimage.php');
?>

Try this code

It is showing as successfully deleted but it is not deleting the record — Nagu, Feb 06 '16 at 06:00
always post what the OP had tried wrong rather just puttin up code — Tharif, Feb 06 '16 at 06:02

Fetching records from database and display empty records once deleted in php mysql

4 Answers4