Viewing Mutliple SQL results

@scottyrobMar 18.2006

Hi i have this code (below), I would like it to select the years 1810 to 1820 from field YEAR… then output it to the page.. BUT i have a next/previous that can view each record.. any ideas?

[code=php]<? require ‘db_connect.php’;

$query = “SELECT * FROM PLATE_BG_CHRISTMAS”; $result = mysql_query($query) or die(“Error: ” . mysql_error() . ” with query $query”);

if (!$result) { $message = ‘Invalid query: ‘ . mysql_error() . “n”; $message .= ‘Whole query: ‘ . $query; die($message); }

while ($row = mysql_fetch_assoc($result)) { echo $row[‘DESCRIPTION’];

mysql_free_result($result); ?>[/code]

to post a comment

PHP

13 Comments(s) _↴

@SheldonMar 18.2006 — #

[code=php]
 <?
 require 'db_connect.php';
 
 $start_year = "1810"; //you could use a $_GET['s']; here
 $end_year = "1820";  // and a $_GET['e']; here
 
 // then set the years like this http://www.domain.com/thispage.php?s=1810&e=1820"
 
 $query = "SELECT * FROM PLATE_BG_CHRISTMAS WHERE years >= '".$start_year."' and years <= '".$end_year."' ";
 $result = mysql_query($query) or die("Error: " . mysql_error() . " with query $query");
 
 if (!$result) {
 $message  = 'Invalid query: ' . mysql_error() . "n";
 $message .= 'Whole query: ' . $query;
 die($message);
 }
 
 while ($row = mysql_fetch_assoc($result)) {
 echo $row['DESCRIPTION'];
 
 }// dont forget to close your query
 
 mysql_free_result($result);
 ?>
 [/code]

@scottyrobauthorMar 18.2006 — #Thanks for that.. Well...

This is my current code (Field is called PROD_DATE and is a TYPE = YEAR field)

[code=php]
 <?
 require 'db_connect.php';
 
 $start_year = "1111"; //you could use a $_GET['s']; here
 $end_year = "9999";  // and a $_GET['e']; here
 
 // then set the years like this http://www.domain.com/thispage.php?s=1810&e=1820"
 
 $query = "SELECT * FROM PLATE_BG_CHRISTMAS WHERE PROD_DATE >= '".$start_year."' and PROD_DATE <= '".$end_year."' ";
 $result = mysql_query($query) or die("Error: " . mysql_error() . " with query $query");
 
 if (!$result) {
 $message  = 'Invalid query: ' . mysql_error() . "n";
 $message .= 'Whole query: ' . $query;
 die($message);
 }
 
 while ($row = mysql_fetch_assoc($result)) {
 echo $row['DESCRIPTION'];
 
 }// dont forget to close your query
 
 mysql_free_result($result);
 ?> 
 [/code]

I have one item in my DB with the PROD_DATE as 2000 but when i run the script i get nothing.... confused...

@LuboxMar 18.2006 — #Hi

Are you sure you can fetch into assoc array when selection all columns (SELECT * FROM). I don't think you can, try to select only description for example SELECT DESCRIPTION FROM....

Lubox

@scottyrobauthorMar 18.2006 — #Thanks for that, it works now.. But.. this dosent work...

http://scott.loddonexplorers.co.uk/PLATE_BG_CHRISTMAS_1800_1810.php?s=1810&e=1820

Like i said the record in my DB is the year 2000 but using the above code dosent make any difference!

Thanks

@SheldonMar 18.2006 — #You can use fetch_assoc with selecting all.

On your link is get "testhello" what am i suposed to get?

@scottyrobauthorMar 18.2006 — #testhello because the dates are now between 1999 and 9999 and the two dates in my DB are 2000 and 1999... So how would i go about using

http://scott.loddonexplorers.co.uk/PLATE_BG_CHRISTMAS_1800_1810.php?s=1810&e=1820

If i use the above i get testhello which i shouldnt get because i should only get entries within 1810 and 1820 (the entries in my db are 2000 and 1999)

@LuboxMar 18.2006 — #Are you assigning the variables the value of the url paramteres?

[code=php]
 $start_year = $_GET["s"];
 $end_year = $_GET["e"];
 [/code]

Lubox

@scottyrobauthorMar 20.2006 — #? Silly me, thanks for that.. how would i go about making a next previous button? I.E i currently have test and hello in my db, but i would like a button so that i can scrool from on to the other

Thanks!

@SheldonMar 20.2006 — #You could use a bit of math to do it easily if your database is sperated evenly

[code=php]
 <?php
 $start_year = $_GET["s"];
 $end_year = $_GET["e"]; 
 
 $increment = "10"; // the differences in the database
 
 $prev_start = ($start_year - $increment);
 $prev_end = $start_year;
 
 
 $next_start = $end_year;
 $next_end = ($end_year + $increment);
 
 echo("<a href="http://www.domain.com/thispage.php?s=".$prev_start."&e=".$prev_end."">Previous</a>");
 
 
 echo("<a href="http://www.domain.com/thispage.php?s=".$next_start."&e=".$next_end."">Next</a>");
 
 ?>
 [/code]

@scottyrobauthorMar 20.2006 — #How would i incorporate it into this code?

<i>
 </i>&lt;?
 require 'db_connect.php';
 
 $start_year = $_GET["s"];
 $end_year = $_GET["e"]; 
 
 $query = "SELECT DESCRIPTION FROM PLATE_BG_CHRISTMAS WHERE PROD_DATE &gt;= '".$start_year."' and PROD_DATE &lt;= '".$end_year."' ";
 $result = mysql_query($query) or die("Error: " . mysql_error() . " with query $query");
 
 if (!$result) {
 $message  = 'Invalid query: ' . mysql_error() . "n";
 $message .= 'Whole query: ' . $query;
 die($message);
 }
 
 while ($row = mysql_fetch_assoc($result)) {
 echo $row['DESCRIPTION'];
 echo $row['PROD_DATE'];
 
 }
 
 mysql_free_result($result);
 ?&gt;

Sorry im new to php and i preffer to learn/learn better by analysing the final code.. if that makes sense

@scottyrobauthorMar 20.2006 — #Anyone?

@scottyrobauthorMar 20.2006 — #Hi, this is my new query

<i>
 </i>$query = "SELECT * FROM PLATE_BG_CHRISTMAS WHERE PROD_DATE &gt;= '".$start_year."' and PROD_DATE &lt;= '".$end_year."' LIMIT 1";

Notice i added the Limit 1 at the end.. So now it limits it to ONE record to display, how would i navigate between each record?

@scottyrobauthorMar 21.2006 — #Anyone?

Also in #PHP _↴

Need help with PHP [RESOLVED] php form question Hiding variables passes in with URL?

Success!

Help @scottyrob spread the word by sharing this article on Twitter...

Tweet This

Viewing Mutliple SQL results

13 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Viewing Mutliple SQL results

13 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

13 Comments(s) _↴

Also in #PHP _↴