retreive and updating mysql database

Question

vipula 0 Newbie Poster

10 Years Ago

I am trying to search mysql databse and display it in an editable form and then update records. I can search and display data in a form. But when I am trying to update data not going to the database. If any one can help me please have a look at my script. I have search.php and update.php two separate files. Can I have both in one file? Thank you in advance

This is Search.php

<?
    $db_host="localhost";
    $db_name=" ";
    $db_user=" ";
    $db_pass=" ";

    $conn=mysql_connect($db_host, $db_user, $db_pass) or die (mysql_error());
    mysql_select_db("$db_name");
?>
<html>
<head>
<meta name="description" content="Php Code  Search & Display Record" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body>

<form name="search" method="post" action="search.php">
<table style=" border:1px solid silver" cellpadding="5px" cellspacing="0px"
align="center" border="0">
<tr>
<td colspan="3" style="background:#0066FF; color:#FFFFFF; fontsize:
20px">Search</td></tr>
<tr>
<td width="140">Enter Search Keyword</td>
<td width="240"><input type="text" name="search" size="40" /></td>
<td width="665"><input type="submit" value="Search" /></td>
</tr>
<tr bgcolor="#666666" style="color:#FFFFFF">
<td>Record ID</td>
<td>Description</td>
<td>&nbsp;</td>
</form>

<?php
    $search=$_POST["search"];

    $conn=mysql_connect($db_host, $db_user, $db_pass) or die (mysql_error());
    mysql_select_db("$db_name");
    $result=mysql_query("SELECT * FROM table WHERE SO  like '%$search%'")or die(mysql_error());

    while ($row= mysql_fetch_array($result) ){
    ?>
    <FORM ACTION="update.php" METHOD=POST>

<input type="text" name="RID" value="<?php echo $row['RID'];?>" size=6> 

Author
<input type="text" name="author" value="<?php echo $row['AU'];?>">

Title
<input type=text name="title" value="<?php echo $row['TI']; ?>" size=30>

Source 
<input type=text name="source" value="<?php echo $row['SO']; ?>" size=40>

 Fulltext Link
<input type=text name="fultext" value="<?php echo $row['fultext']; ?>" size=40> <input type=submit name="submit" value="Update"></p><br>

</form>

<?php
      }

    ?>

</table>
</form>
</body>
</html>

This is update.php

<html>
<head>
<title>Update a Record in MySQL Database</title>
</head>
<body>

<?php

$db_host="";
$db_name="";
$db_user="";
$db_pass="";

$conn=mysql_connect($db_host, $db_user, $db_pass) or die (mysql_error());
mysql_select_db("$db_name");

 $RID=$_POST['RID'];
 $AU=$_POST['AU'];
 $SO=$_POST['SO'];
 $FULTEXT=$_POST['FULTEXT'];
 $query="update table SET AU='$AU', SO='$SO',FULTEXT='$FULTEXT' WHERE RID=$RID";
 mysql_query($query);
 echo "<center>Successfully Updated in DATABASE</center>";
 include("search.php");

 ?>

</body>
</html>

mysql php

Edited 10 Years Ago by pritaeas because: Fixed markdown.

4 Contributors
26 Replies
269 Views
7 Hours Discussion Span
Latest Post 10 Years Ago Latest Post by minitauros

ryantroop 177 Practically a Master Poster

10 Years Ago

ahh my mistake I misread your question.

Yes, you can have both in the same file. PHP is quite good at this.

You simply need to check the $_POST vars coming at the top of the file, and process things as needed.

You can check
isset($_POST)

and that will let you know if something is coming in for an update, otherwise you can run the script normally.

so..

if (isset($_POST) && $_POST['search'] != '')
{
  //run update sql
}
else
{
  //show form? do whatever...
}

//you can even do the rest of the page here, and still show the page after the update :)

minitauros 151 Junior Poster

10 Years Ago

I would like to suggest a (in my opinion) cleaner solution :). This solution is as follows:

Create a database file, in which you create your database connection, etc. You can include that file in every file that you need a database connection in. This way, you don't need to modify your database connection related code in every file if you once decide to change your database info. You can include a file using PHP's include(), include_once(), require() or require_once() functions. Look them up on php.net ;).

I myself like to have separate files for each action/process, but in this particular situation, I guess you could have 2 forms on one page, and have the one that matches the action (e.g. update) be shown. That would go, for example, as follows (this is actually pretty much the same as vipula suggests):

<?php
// Include the file that manages your database connection.
include('includes/database_connection.php');

// Find out if we need to display the search form or the update form.
if($_POST['search'])
{
    //* A search query has been given. Display the update form.

    // Perform queries.
    $query = 'SELECT ...';
    // etc..
    ?>

    <form>
        <!-- Display your HTML stuff here... -->
    </form>

    <?php
}
else
{
    //* A search query wasn't given. Display the search form.

    // Perform queries.
    $query = '';
    // etc...
    ?>

    <form>
        <!-- Display your HTML stuff here... -->
    </form>

    <?php
}

Edited 10 Years Ago by minitauros

minitauros 151 Junior Poster

10 Years Ago

Please read my post carefully again, as I think you might benefit from it in the future. If you have questions about it, feel free to ask! Here's an example of what you could do:

<?php
/***********************************************************************
Create database connection
*/

$db_host="localhost";
$db_name="";
$db_user="";
$db_pass="";
$conn = mysql_connect($db_host, $db_user, $db_pass) or die (mysql_error());

// Select database. Your line - mysql_select_db("$db_name"); - was okay, but you don't need to use
// quotes here :).
mysql_select_db($db_name);


/***********************************************************************
HTML output
*/
?>

<html>
<head>
    <meta name="description" content="Php Code Search & Display Record" />
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body>

    <?php
    // Find out which form/action we need to load. I have created some
    // hidden input fields to help identifying the forms.
    if(!$_POST['search'])
    {
        //* No search query has been submitted yet. Display the search form.
        ?>

        <form name="search" method="post" action="search.php">
            <input type="hidden" name="form_name" value="search">
            <table style=" border:1px solid silver" cellpadding="5px" cellspacing="0px" align="center" border="0">
                <tr>
                    <td colspan="3" style="background:#0066FF; color:#FFFFFF; fontsize: 20px">
                        Search
                    </td>
                </tr>
                <tr>
                    <td width="140">Enter Search Keyword</td>
                    <td width="240"><input type="text" name="search" size="40" /></td>
                    <td width="665"><input type="submit" value="Search" /></td>
                </tr>
                <tr bgcolor="#666666" style="color:#FFFFFF">
                    <td>Record ID</td>
                    <td>Description</td>
                    <td> </td>
                </tr>
            </table>
        </form>

        <?php
    }
    elseif($_POST['form_name'] == 'search' && $_POST['search'])
    {
        //* The search form has been submitted. Display the update form(s).
        $search = $_POST["search"];
        $result = mysql_query("SELECT * FROM saudi_journal WHERE SO like '%$search%'")or die(mysql_error());

        while($row = mysql_fetch_array($result))
        {
            ?>
            <form action="search.php" method="post">
                <input type="hidden" name="form_name" value="update">
                <input type="text" name="RID" value="<?php echo $row['RID'];?>" size=6>
                Author
                <input type="text" name="author" value="<?php echo $row['AU'];?>">
                Title
                <input type=text name="title" value="<?php echo $row['TI']; ?>" size=30>
                Source
                <input type=text name="source" value="<?php echo $row['SO']; ?>" size=40>
                Fulltext Link
                <input type=text name="fultext" value="<?php echo $row['fultext']; ?>" size=40> <input type=submit name="submit" value="submit"></p><br>
            </form>
            <?php
        }
    }
    elseif($_POST['form_name'] == 'update')
    {
        //* The update form has been submitted. Update the database.
        if (isset($_POST['submit'])) 
        {
            $RID = $_POST['RID'];
            $AU = $_POST['AU'];
            $TI = $_POST['TI'];
            $SO = $_POST['SO'];
            $query = "update saudi_journal SET FULTEXT='$FULTEXT' WHERE RID='$RID'";
            mysql_query($query);
            echo "<center>Successfully Updated in DATABASE</center>";
        }
    }
    ?>

</body>
</html>

Edited 10 Years Ago by minitauros

minitauros 151 Junior Poster

10 Years Ago

Oh I see there is still a if (isset($_POST['submit'])) line in the second elseif() statement. You could remove that - the if() statement that comes a couple of lines earlier should do the work.

Are you getting any errors or is it just not updating?

You don't have to name any of the forms. I have included a hidden <input> in each form, that holds its name. That's what is being checked :).

minitauros 151 Junior Poster

10 Years Ago

Could you replace the last elseif() construction by the following?

elseif($_POST['form_name'] == 'update')
{
    //* The update form has been submitted. Update the database.

    // Let's see if we actually arrive at this part:
    echo '<p>Run the update...</p>';

    $RID = $_POST['RID'];
    $AU = $_POST['AU'];
    $TI = $_POST['TI'];
    $SO = $_POST['SO'];
    $query = "update saudi_journal SET FULTEXT='$FULTEXT' WHERE RID='$RID'";
    mysql_query($query);
    echo "<center>Successfully Updated in DATABASE</center>";
}

And could you add the following line BEFORE the first if() statement?

echo '<p>The following data has been submitted:</p><pre>'; print_r($_POST); echo '</pre>';

It should give you some more info on what is happening, so that you can trace why it's not working as you want it to.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

ryantroop 177 Practically a Master Poster · Answer 1 · 2013-12-11T05:22:08+00:00

Line 20 says action='search.php' where your script Name for the processing page seems to be Update.php
Try changing it and see if that helps.

vipula 0 Newbie Poster · Answer 2 · 2013-12-11T05:41:10+00:00

It wont work. That page is search and display records. Further below is the update.php. anyway thanks for your reply..

deepyseo 0 Newbie Poster · Answer 3 · 2013-12-11T05:43:50+00:00

nice information has shared i have got knowledge over here.

vipula 0 Newbie Poster · Answer 4 · 2013-12-11T07:02:39+00:00

Thanks my dear I will try that and let you know..

vipula 0 Newbie Poster · Answer 5 · 2013-12-11T08:42:22+00:00

I cant figur out to do this. I can display retrieved data in a form. But when I update data wont go to database. I cant cath data from form to update script. I am trying to have all in one page. Here is my script again I will put if someone can edit it for me please it will be a greate help.

<?php

    $db_host="localhost";
    $db_name="";
    $db_user="";
    $db_pass="";

    $conn=mysql_connect($db_host, $db_user, $db_pass) or die (mysql_error());
    mysql_select_db("$db_name");

?>
<html>
<head>
<meta name="description" content="Php Code  Search & Display Record" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<form name="search" method="post" action="search.php">
<table style=" border:1px solid silver" cellpadding="5px" cellspacing="0px"
align="center" border="0">
<tr>
<td colspan="3" style="background:#0066FF; color:#FFFFFF; fontsize:
20px">Search</td></tr>
<tr>
<td width="140">Enter Search Keyword</td>
<td width="240"><input type="text" name="search" size="40" /></td>
<td width="665"><input type="submit" value="Search" /></td>
</tr>
<tr bgcolor="#666666" style="color:#FFFFFF">
<td>Record ID</td>
<td>Description</td>
<td> </td>
</form>


<?php

$search=$_POST["search"];


    $conn=mysql_connect($db_host, $db_user, $db_pass) or die (mysql_error());
    mysql_select_db("$db_name");
    $result=mysql_query("SELECT * FROM saudi_journal WHERE SO  like '%$search%'")or die(mysql_error());

    while ($row= mysql_fetch_array($result) ){
    ?>
    <FORM ACTION="search.php" METHOD=POST>
<input type="text" name="RID" value="<?php echo $row['RID'];?>" size=6> 
Author
<input type="text" name="author" value="<?php echo $row['AU'];?>">
Title
<input type=text name="title" value="<?php echo $row['TI']; ?>" size=30>
Source 
<input type=text name="source" value="<?php echo $row['SO']; ?>" size=40>
 Fulltext Link
<input type=text name="fultext" value="<?php echo $row['fultext']; ?>" size=40> <input type=submit name="submit" value="submit"></p><br>
</form>
<?php
      }
    if (isset($_POST['submit'])) {  

$RID=$_POST['RID']; 
$AU=$_POST['AU']; 
$TI=$_POST['TI']; 
$SO=$_POST['SO']; 


$query="update saudi_journal SET FULTEXT='$FULTEXT' WHERE RID='$RID'";
mysql_query($query);
echo "<center>Successfully Updated in DATABASE</center>";
}
    ?>


</table>

</body>
</html>

vipula 0 Newbie Poster · Answer 6 · 2013-12-11T09:20:03+00:00

I tried it Sir but stil not updating the database. do I have to name the second form as update? Thank you so much for spending your time to edit this. God bless u

vipula 0 Newbie Poster · Answer 7 · 2013-12-11T09:51:01+00:00

I dont get any errors. Its not updating the database. Can u please recorrect the script for me Sir. I did not get what u said exactly. Its a Great help

vipula 0 Newbie Poster · Answer 8 · 2013-12-11T10:15:16+00:00

When I echo above print line it shows what I searched like below.

Array
(
    [form_name] => search
    [search] => Journal of the Saudi Heart Association 2005 September; 17(3): 167-175
)

vipula 0 Newbie Poster · Answer 9 · 2013-12-11T10:20:32+00:00

When I replace the 2nd elseif with new elsif stament it shows following

The following data has been submitted:

Array
(
    [form_name] => update
    [RID] => 3
    [author] => Motabagani MA , Sonhalla A
    [title] => Anatomical variations of the branching pattern of the left coronary artery in the human heart
    [source] => Journal of the Saudi Heart Association 2005 September; 17(3): 167-175
    [fultext] => www.google.com
    [submit] => submit
)

minitauros 151 Junior Poster Featured Poster · Answer 10 · 2013-12-11T10:25:43+00:00

Woops, my bad :). The first if() statement is not doing a proper check. It should be

if(!$_POST['form_name'])

vipula 0 Newbie Poster · Answer 11 · 2013-12-11T10:41:35+00:00

Sorry to bother u so much. Still not updating it. i get the following

The following data has been submitted:

Array
(
    [form_name] => update
    [RID] => 3
    [author] => vip
    [title] => Anatomical variations of the branching pattern of the left coronary artery in the human heart
    [source] => Journal of the Saudi Heart Association 2005 September; 17(3): 167-175
    [fultext] => www.yahoo.com
    [submit] => submit
)
Run the update...

Successfully Updated in DATABASE
Successfully Updated in DATABASE

vipula 0 Newbie Poster · Answer 12 · 2013-12-11T10:42:19+00:00

vipula 0 Newbie Poster

10 Years Ago

I cant figure out whats wrong. Stil cant update

Edited 10 Years Ago by vipula

minitauros 151 Junior Poster Featured Poster · Answer 13 · 2013-12-11T11:02:56+00:00

Well, it could be because you're not actually specifying a value to update your record with. You could try this to see if an error is generated. If not, I guess it's your query that is using faulty data.

$RID = $_POST['RID'];
$AU = $_POST['AU'];
$TI = $_POST['TI'];
$SO = $_POST['SO'];

// In your query, $FULTEXT is probably null, or does it get defined somewhere else?
$query = "UPDATE saudi_journal SET FULTEXT = '$FULTEXT' WHERE RID = '$RID'";

echo '<p>The query that was executed, is as follows:<br>' . $query . '</p>';

mysql_query($query);
$error = mysql_error();

if($error)
    echo $error;
else
    echo "<center>Successfully Updated in DATABASE</center>";

vipula 0 Newbie Poster · Answer 14 · 2013-12-11T11:13:38+00:00

I notice onething now Sir. Fulltext field is not null. There is a url Address. When I update it it deleted the data in that field. But AU field nothing happen.Fultext field has a url. But some dont have a url. it has a default value of "No fulltext available" All I want is to edit records and add url to all these records

When I run with above I get this
The following data has been submitted:

Array
(
    [form_name] => update
    [RID] => 3
    [author] => vipula
    [title] => Anatomical variations of the branching pattern of the left coronary artery in the human heart
    [source] => Journal of the Saudi Heart Association 2005 September; 17(3): 167-175
    [fultext] => www.google.com
    [submit] => submit
)
The query that was executed, is as follows:
UPDATE saudi_journal SET FULTEXT = '' WHERE RID = '3'

Successfully Updated in DATABASE

minitauros 151 Junior Poster Featured Poster · Answer 15 · 2013-12-11T11:29:30+00:00

Well I guess then you'd have to assign the URL you want to add to a record to a variable, like $url = 'your_url', and then update your database, for example 'UPDATE table_name SET url = "' . mysql_real_escape_string($url) . '" WHERE fill_in_key = "' . mysql_real_escape_string($key) . '" (this looks like your current query).

vipula 0 Newbie Poster · Answer 16 · 2013-12-11T11:53:41+00:00

When it run the update quary it will add a null value to that field and it becomes empty. Seems somthing wrong with the query? It is not because of the url. I tried this with another AU field which include Author names. Same happened that field also replaced with emply space.
I tried the following also
$query = "UPDATE saudi_journal SET AU ='$AU' WHERE RID = '$RID'";
Got the following

The following data has been submitted:

Array
(
    [form_name] => update
    [RID] => 3
    [author] => vipula
    [title] => Anatomical variations of the branching pattern of the left coronary artery in the human heart
    [source] => Journal of the Saudi Heart Association 2005 September; 17(3): 167-175
    [fultext] => 
    [submit] => submit
)
The query that was executed, is as follows:
UPDATE saudi_journal SET AU ='' WHERE RID = '3'

Successfully Updated in DATABASE

Still not worked. When I tried to update It deleted the AU field

minitauros 151 Junior Poster Featured Poster · Answer 17 · 2013-12-11T12:02:33+00:00

Well, have you checked if the variables you are using in your query actually have a value? Does echo-ing your query show anything particular? E.g. what does echo $query; say? Are the variables filled in, or are they empty?

vipula 0 Newbie Poster · Answer 18 · 2013-12-11T12:09:26+00:00

Yes Sir it has a value. Echo query showsthe following. Instead of fultext I tried with AU field. Still Same

The query that was executed, is as follows:
UPDATE saudi_journal SET AU ='' WHERE RID = '3'

This is what I get when I echo query.

All I want to do is to edit the data which I retrieve by the search and update back to the database.
Now only problem is data is not updated. insted it replace with empty space (delete what I have in that field)

minitauros 151 Junior Poster Featured Poster · Answer 19 · 2013-12-11T12:46:03+00:00

UPDATE saudi_journal SET AU ='' WHERE RID = '3'

This query sets AU to null (removes its value). You should make sure $AU has a value in your script (so that AU = '"$AU"' will actually work) :).

vipula 0 Newbie Poster · Answer 20 · 2013-12-11T12:50:17+00:00

Thank you my dear finally I got it working. In the update form I change the names to author to AU title to TI and so on to match the field names in the database. then its working.

Many thanks "minitauros" for your kindness to help me.

have a gr8 day

minitauros 151 Junior Poster Featured Poster · Answer 21 · 2013-12-11T12:57:53+00:00

minitauros 151 Junior Poster

10 Years Ago

You're welcome :). Glad you got it to work.