Populate a text box with MSQL Query

Anon

I was wondering if anyone knows where I can find a simple example of a MYSQL and PHP database CODE.

Specifically, I set up a PHP/MYSQL database with PHP Front. I have an interface to load the data and it works fine already. I can also specifically query a record and show the results. BUT, I want to increment it, 1 record at a time by using a "NEXT" button.

I would appreciate a snippet of code if possible or a somewhere to find an actual running application.

Thanks Very Much in advance..
Vin

Anon

You need to use the LIMIT and OFFSET functions so your script only returns one set of results at a time.

<?php

//firstly you set offset at 0
$offset = "0";

//now you have to count the records that are available to be shown.

$res = mysql_query("SELECT count() FROM $table");
$records = mysql_result($res,0,"count()")

//now you can do the actual query using the limit function so you get only 1 result.

$sql=mysql_query("SELECT * FROM $table LIMIT $offset,1")
while($row=mysql_fetch_array($sql))
{
//show the data in here
<textarea>$row[field1];</textarea>
}

//now you can set up a link to display the next result simply by setting the $offset to the next number above what it was last time.

print ("<a href='$PHP_SELF?offset=".($offset+1)."'>Next</a>

//but it won't know when to stop displaying the next link so you need to find out how many results are left to show.

//find # of results
$results = "".($records-$offset)."";

//now you can run a basic if/else statement

if($results < 1)
{
//do nothing cos this is the last record
}

else
{
//display that link with the offset
}

HTH
scott d~

Anon

And is it possible to retrieve some lines of data and each one with a link to perform a new query?. The easiest example I think is to search in a table of articles by name (or the beginning of the name) and when your get some of them, you need to pick in one of them to see a complete description. I need to do that with a drugs database.
Thanks.
Antonio.

Anon

Then you would simply have the script first display a couple of lines from each result with a link next to it (or the result itself) which would have a variable in it that can be used for another search.

ie:

//Your original search might be..

mysql_query("SELECT * FROM $table WHERE title LIKE '$keyword'");

//but so you can also allow a more accurate search you could set it up like this.

<?php

if($keyword)
{
$query = "LIKE";
}
if($title)
{
$query = "=";
$keyword = $title;
}

//so now you do your query..
$sql = mysql_query("SELECT * FROM $table WHERE title $query '$keyword'");

//and in you results you have he link display the link

while($row = mysql_fetch_row($sql))
{
$title = $row[0];
$result = $row[1];
//blah blah

//show result as link
print ("<a href='$PHP_SELF?title=$title'>$result</a>");

}

kind of like that.....I hope you can follow ok!

rgds,
scott d~

Anon

Thanks Scottd,
The program runs fine, and with a link which performs a search with a distinct field!
Here is the code for anyone who is interested:
<?
//define variables to connect
$dbname = "db name";
$dbhost = "host";
$dbuser = "your user";
$dbpass = "your pass";
$conn = mysql_pConnect($dbhost,$dbuser,$dbpass);

    // connect to server
    $mysql_link = $conn;

    // select the 'store' database
    mysql_select_db($dbname, $mysql_link);

if(strlen($keyword)>1)
$keyword = $keyword . "%";
//I'm going to use two tables in the query
$table1 = "nomencla";
$table2 = "labora";
if($keyword)
{
$query = "LIKE";
$campo = "nomgen";
}
//In the second search, I'll use a distinct field
if($title)
{
$query = "=";
$keyword = $title;
$campo = "codesp";
}

//so now you do your query..
$sql = mysql_query("SELECT * FROM $table1,$table2 WHERE
labora=clab and $campo $query '$keyword'");

//and in you results you have he link display the link
while($row = mysql_fetch_row($sql))
{
$cod = $row[0];
$lab = $row[1];
$estado = $row[2];
$gt = $row[3];
$pvp = $row[4];
$title = $row[5];
$nomesp = $row[6];
$fbaja = $row[7];
$generico = $row[8];

$fbaja = $row[7];
$generico = $row[8];
$nomlab = $row[11];

//show result as link or different if it's the second query
if($campo=="nomgen")
{
print ("<a href='$PHP_SELF?title=$cod'>
$title $nomesp</a>");
print ("<BR>");
}
else
{
print("<FONT FACE=arial COLOR=red SIZE=+1>");

    print ("<a href='$PHP_SELF?title=$cod'></a>
    CODIGO NACIONAL: $cod <BR>
    NOMBRE: $title  $nomesp <BR>
    PVP: $pvp <BR>
    GENERICO: $generico <BR>
    SITUACION: $estado <BR>
    LABORATORIO: $nomlab <BR>");

    SITUACION: $estado <BR>
    LABORATORIO: $nomlab <BR>");

print("</FONT>");

}

If you think it could be improved, tell me please.
Thanks a lot.
Antonio.