Question about displaying and changing data

[Bar2aYunie]

Hello,

I've got a MySQL db which contain addresses. I also made a script to easily add new entries to the db with a number of columns.

Now, I do know how to make a script to display what's in the db... But I would like to know how to make a script to not only display what's in the db, but also how to modify it when being displayed.

So let's say I want to display what's in the db. So I need to select what I am looking for. So let's say all entries that have the same country. Then, I want to walk through them. Meaning, I am viewing one entry (with all of the fields) and then I can modify all of the fields while viewing. When done, I need to click on next, which automatically saves the changes. Then, the next entry will be displayed. Untill there are no more and a message is shown, or I'll be redirected to the search page.

That is the idea, but can anyone help me out or at least point me in the right direction?

Thank you very much!!

Yunie

[fastsol1]

It's pretty easy really. Simply gather the info from the db like normal and then echo the info into a form, each row from the db would go into a textbox or textarea or what ever you need it to be and then you edit it just like a normal form works and then hit submit and run an update query to change the info in the db.

[Bar2aYunie]

The thing is, how do I do that last bit... 'run an update query'?
It does sound easy to just gather info, echo in form and then put in textbox. That shouldn't give any difficulties. But how do I update it so it is changed in the db?

[Bar2aYunie]

Okay, I've build the script to display all of the entries (one per page) in textboxes.

I also have another script, similar to it, but that is meant to add completely new entries.

On both scripts, I use a form to insert everything. Here's the start of the form:


<form name=form1 method=post action=add.php onsubmit='return validate(this)'><input type=hidden name=todo value=post>

And the submit button:


<input type=submit value=Submit>

The entries are called and added the following way: (just stated one)


<input type='text' name='name' value='{$row['name']}'>

On the add.php page the $todo is stated:



<?php
if(isset($todo) and $todo=="post"){

$status = "OK";
$msg="";

if($status="OK"){
echo "<font face='Verdana' size='2' color=red>$msg</font><br><input type='button' value='Retry' onClick='history.go(-1)'>";
}else{
if(mysql_query("insert into addresses(id,name,address,postalcode,city,phone) values('$id','$name','$address','$postalcode','$city','$phone')")){
echo "<font face='Verdana' size='2' color=green>Record succesfully added</font>";}
else{ echo "Database Problem, please contact Site admin";
//echo mysql_error();
}

}
}
?>


Now, I don't know how to change this part so that it'll update the entries, instead of adding a new entry. Do you know what to change? Thanx!


Ps. Sorry forgot to edit last message instead of post new.

[djr33]

"UPDATE" is a type of MySQL query you will need to learn. They're not particularly difficult.

Technical manual:
http://dev.mysql.com/doc/refman/5.0/en/update.html

Slightly easier to read:
http://www.tizag.com/mysqlTutorial/mysqlupdate.php

[midhul]

Note: Sorry, by the time I wrote my post, Bar2aUnie made a new post. Still hopes this helps as an example of update


You can use the SQL UPDATE query to modify records in you db.

Assuming that you've built your form, and got the required db values into text boxes,
now after the user clicks the next button (to submit the form), you request that data, get it into variables, and use the UPDATE query to modify your db.

The syntax of the UPDATE query is simple :



mysql_query("UPDATE table_name SET column_name WHERE anyother_column = 'something'");


Here is a example, which is probably similar to your case:

Lets say you have a table (friends):

FirstName LastName Country
Midhul Varma India
Dummy xyz India


Now after you select the fields with same country and get it into form, and it is submitted the php script would be something like:
(form fields are firstname and lastname -> both text boxes)




$fname = $_REQUEST ['firstname'];
$lname = $_REQUEST['lastname'];

// Set up mysql connection, and select you db

$everything_in_db = mysql_query("SELECT * FROM friends");
$num = mysql_numrows($everything_in_db);

$i = 0;
while($i < $num) {

$first_name = mysql_result($everything_in_db, $i, "FirstName"); //get current value of field

$last_name = mysql_result($everything_in_db, $i, "Lastname");

if ($fname != $first_name) { //check if value has been changed

mysql_query("UPDATE friends SET FirstName = '$fname' WHERE FirstName = '$first_name' AND LastName = '$last_name' ); //UPDATE DB

}

if ($lname != $last_name) {

mysql_query("UPDATE friends SET LastName = '$lname' WHERE FirstName = '$first_name' AND LastName = '$last_name' );

}


// Display form, with values of i+1

$i++

}

mysql_query("UPDATE friends SET FirstName = '$fname', SET LastName = '$lname' WHERE ");

[Bar2aYunie]

Well, the thing is... I already have a query... So I'm not sure how to add another one... I wanted to view only one entry per page, and only by clicking next or actually '>' which acts as a next button, you'll view the next entry. Then, it's needed to calculate the number of pages to display that there.

So I don't know how to use both that and your query. If you know of another way to make sure everything is displayed as one entry per page with your query, then plz lemme know.

Here's the query I have now:


<?php



$id = $_GET['id'];



$maxcol = 1;

$result = mysql_query("SELECT * FROM addresses order by id desc") or die (mysql_error());

$count = mysql_num_rows($result);

$rows = ceil($count/$maxcol);

echo "<table>";



$limit = 1;

$table = 'addresses';

//get requested page

$page = empty($_GET['page']) ? 1 : (int) $_GET['page'];

//calculate offset

$offset = ($page - 1) * $limit;



//construct query

$query = "Select SQL_CALC_FOUND_ROWS * from $table order by id desc LIMIT $limit OFFSET $offset";



//execute query

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

$cResult = mysql_query("Select found_rows()") or die(mysql_error());

list($count) = mysql_fetch_array($cResult, MYSQL_NUM);



$pageNavBar = getPages($limit, $count, $page);

echo <<<HTML

// after this part the form will be displayed in a table. ?>

After the table and the form, the code ends by calculating and displaying the > for next and so on. I made this code earlier for a table which will display 20 entries per page, in two columns. So maybe there's some code that can be left out. But I'm not sure what. I used this code so I wouldn't have to rewrite it all. And since that will take up a long time, I just figured I'd use this code and set the limit to 1 per page.

ending code:



<?php
HTML;

$row = mysql_fetch_assoc($result);

if (!$row){

echo "<td>&nbsp;</td><td>&nbsp;</td>";

} else {

echo <<<HTML

<td align="left" width="20%">
<br><br>

<p></td>

HTML;

} //end if

echo "</tr>";

} //end while



echo <<<HTML

</tbody>

<tfoot>

<tr>

<p><th colspan="4">

$pageNavBar

</th>

</tr>

</tfoot>

</table>

HTML;



function getPages($limit, $count, $page){

//put the url into a variable

$s = "http://" . $_SERVER['HTTP_HOST'] .'/'. ltrim($_SERVER["SCRIPT_NAME"] ,'/');

//calculate the number of pages needed

$nPages = ceil($count/$limit);

//do the first/last prev/next buttons

$first = <<<HTML

<span class="first navi">

<a href="$s?page=1"><<<</a>

</span>

HTML;

$last = <<<HTML

<span class="last navi">

<a href="$s?page=$nPages">>>></a>

</span>

HTML;

if ($page > 1) {

$p = $page - 1;

$prev = <<<HTML

<span class="next navi">

<a href="$s?page=$p"><</a>

</span>

HTML;

} else {

$prev = '&nbsp;';

$first = '&nbsp;';

}

if ($page < $nPages) {

$p = $page + 1;

$next = <<<HTML

<span class="next navi">

<a href="$s?page=$p">></a>

</span>

HTML;

} else {

$next = '&nbsp;';

$last = '&nbsp;';

}



//now construct the pages

//if more than 10 then use a select

if ($nPages > 10){

$output = <<<HTMLJS

<span class="navi select">

<select name="page" onchange="window.location='{$s}?page=' + this.options[this.selectedIndex].value;">

HTMLJS;



for ($p=1; $p <=$nPages; $p++){

$output .= "<option value=\"$p\">$p</option>";

}

$output .= '</select></span>';

} else {

$output = '';

for ($p=1; $p<=$nPages; $p++){

$active = ($p == $page) ? 'active' : '';

$output .= "<span class=\"navi page {$active}\"><a href=\"$s?page=$p\">$p</a></span>";

}

}

return '<div class="pageNav">' . $first . $prev. $output . $next . $last . '</div>';

}

?>


I'll also try to figure out how to add in ur code. Thanx again!

[midhul]

I'm not really getting your idea of coding it.
So, I made a working example, with a 3 rowed table.

Table name: friends
http://i954.photobucket.com/albums/ae27/superquark/tablemysql.jpg

mysdb_friends.php




<?php

$id = $_GET['i'];

$con = mysql_connect("localhost","midhul","root");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("test", $con);

$result = mysql_query("SELECT * FROM friends");

if ($id > 0) {

$bid = $id - 1;

$firstn = $_POST['firstn'];
$lastn = $_POST['lastn'];


$i_fname = mysql_result($result, $bid, "FirstName");
$i_lname= mysql_result($result, $bid, "LastName");
$i_country = mysql_result($result, $bid, "Country");

if ($firstn != $i_fname && !empty($firstn)) { mysql_query("UPDATE friends SET FirstName = '$firstn' WHERE FirstName = '$i_fname' AND LastName = '$i_lname'"); }
if ($lastn != $i_lname && !empty($lastn)) { mysql_query("UPDATE friends SET LastName = '$lastn' WHERE FirstName = '$i_fname' AND LastName = '$i_lname'"); }


}





$fname = mysql_result($result, $id, "FirstName");
$lname = mysql_result($result, $id, "LastName");
$country = mysql_result($result, $id, "Country");

$nid = $id + 1;

echo "<form method=post action=mysdb_friends.php?i=" . $nid . ">" ;
echo "<input type=text name=firstn value=" . $fname . ">";
echo "<input type=text name=lastn value=" . $lname . ">";
echo "<input type=text name=cntry disabled=true value=" . $country . "><br>";
echo "<input type=submit value=next>";


?>


Just run the PHP code without any arguments (ex: http://localhost/mysdb_friends.php), it should automatically take i = 0, and hence show the first row.
As you go on pressing 'next', the id keeps incrmenting.

Hope it will help

P.S: Make sure u add stuff like issets etc... I wrote this very quickly, so its rough, though it works. Also, remember to set limit for $id, or after the last row, you will get an error