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

[kekkaishi]

Yunie,
You might benefit from following a tutorial on how to update database records. This one here is a good tutorial. I believe that going through this tutorial would give you the basic idea on how to achieve your goal. If you got stuck en route, you could always ask for the forum's help by sharing your issue (and code).

Regards.

[sainigks]

Hi

You can do this .

first of all you have to execute a query to find minimum id in table which is the first id in you table.

after that you have to use update query for that id.

when that data will change use loop to increase id value . and again get the next id and do the same . update data.

loop will do until you have changed all the entries

-Gajendra

[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? The site you directed me to is good, but it uses a completely different method and I don't know how to use that, since I already have a result query...

Thanx!

[kekkaishi]

"Okay, I've build the script to display all of the entries (one per page) in textboxes. "
If you already have done it then you need to have a separate file called, maybe, update.php. This file would be similar to your add.php, but instead of the insert query, you will need an update query. Post information to update.php file when you click the submit button (that is your next button). Your update query would be similar to the following line of code.

mysql_query("update addresses SET id={$id}, name='{$name}'");

Upon successfully updating, you could redirect the browser to the "next" records page (Referring to your remark (quoted above), I am assuming you are already able to view the separate records by pressing a "next" button)

Hope this made sense. :)

[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! Oh and by the way, it made perfectly sense, but I don't know how to use it.... :S

[kekkaishi]

From what I understand from your description, you are displaying all the information in a form in input fields or of such. Why don't you add a submit button (to save the data) to the bottom of the form (above the "next" link). Now what you need to understand is that this would act as a normal form, just like the one you use to insert data into database. I will include an example below.

//your navigation (pagination) code with necessary mysql queries
<form method="post" action="update.php">
<input type='text' name='name' value='{$row['name']}' />
//all other fields
<input type="submit" value="Save" />
</form>
//navigation (prev, next and all)

This form is posting information to update.php. It would look similar to the add.php page. Only difference would be that, as I said earlier, instead of the insert query, use an update query. When the query is completed you would need to redirect the browser to the previous page you were in.
One way to achieve this would be to use a hidden field in the form to send the current page to update.php and use this link to redirect the page. (Skimming through your code, I see that the your next page link is like this. <a href="$s?page=$p"> where $p is $page+1. So your current page would be $page...)

Another approach to achieve your goal is to use Ajax to post information to update.php. This is to eliminate the redirecting process. (I recommend using Ajax if you are familiar with it).

Hope the information I provided would allow you to carry on.