Code Help

[erikhillis]

I need someone to insert some code into my current code... what I need is a limit results per page, and links to the other pages... like:

Page 1 2-3-4-5

I have made limiting results and next page results before, but my code now is too advanced for me to know where to modify and add the new bits of code.

<?php   

$db = mysql_connect("localhost","---","---");
mysql_select_db (---);
$sql = "SELECT * FROM buildings WHERE id='{$_REQUEST['id']}' and STATUS='{$_REQUEST['STATUS']}' order by height_m desc limit 50";  

if (!$result = mysql_query($sql)) {   

    print "Invalid query ($sql) : " . mysql_error();   

    exit;   

}   

$num = mysql_num_rows($result);   

$buildingid = $row[buildingid];
$name = $row[name];
$height_m = $row[height_m];
$city = $row[city];
$description = $row[description];
$floors = $row[floors];
$imageurl = $row[imageurl];
$STATUS = $row[STATUS];
$year = $row[year];
$started = $row[started];
$structural_type = $row[structural_type];
$photos = $row[photos];
$height_ft = $row[height_ft];
$province = $row[province];
$id = $row[id];
$num = mysql_num_rows($result); 

if ($num < 1) {   

echo "<font color=#000000 face=Arial size=2>Please select the type of buildings you want to see on the left.";   

}    

else    

{   

while($row = mysql_fetch_array($result)) { 
echo "<table border=0 width=100% cellspacing=0 cellpadding=0>
        <tr>
          <td width=35% valign=top><p align=left>&nbsp;<A href=urbanca.php?page=building?buildingid=$row[buildingid]><font face=Arial style= 'font-size: 8pt'>$row[name]</B></a></font></b></td>
          <td width=13% valign=top><font face= 'Arial' style= 'font-size: 8pt'><center>$row[height_m] m</font></b></td>
          <td width=13% valign=top><center><font face=Arial style= 'font-size: 8pt'>$row[floors]</font></b></td>
          <td width=13% valign=top><center><font face=Arial style= 'font-size: 8pt'>$row[STATUS]</font></b></td>
          <td width=13% valign=top><center><font face=Arial style= 'font-size: 8pt'>$row[structural_type]</font></b></td>
          <td width=13% valign=top><center><font face=Arial style= 'font-size: 8pt'>$row[year]</font></b></td>
        </tr>
      </table>";   

  } 
}   

?>

[tekky]

do a search for pagination, I'm fairly certain in the code snippets forum or whatever they named it... where people post code to be critique'd/reviewed... there was a pagination script posted...

basic example...
you set a variable as your limit.. say 10 posts per page
you then track the CURRENT starting post... in this case page 1 starts at post 1
so then you make your SQL query read something like
SELECT * from mytable WHERE x=y limit curstartingpost,limit

in this case it would send limit 1,10

start at 1, and return 10 rows

make your LINKS to the next pages send a new start # so page 4 would be 40-49 or 41-50 however you work that out

[erikhillis]

Here is my code now (below) and here is a sample page of the error: Maybe you could tell me where I went wrong...

http://www.urbancanada.com/urbanca.php?page=buildings&id=1&STATUS=Complete

<?php   

$db = mysql_connect("localhost","uc_site","----------");
mysql_select_db (uc_site);
if(!isset($_GET['page'])){ 
    $page = 1; 
} else { 
    $page = $_GET['page']; 
} 

$max_results = 50; 

$from = (($page * $max_results) - $max_results); 

$sql = "SELECT * FROM buildings WHERE id='{$_REQUEST['id']}' and STATUS='{$_REQUEST['STATUS']}' order by height_m desc limit 50"; 
$num = mysql_num_rows($result);   

$buildingid = $row[buildingid];
$name = $row[name];
$height_m = $row[height_m];
$city = $row[city];
$description = $row[description];
$floors = $row[floors];
$imageurl = $row[imageurl];
$STATUS = $row[STATUS];
$year = $row[year];
$started = $row[started];
$structural_type = $row[structural_type];
$photos = $row[photos];
$height_ft = $row[height_ft];
$province = $row[province];
$id = $row[id];
$num = mysql_num_rows($result);
while($row = mysql_fetch_array($sql)) { 

if ($num < 1) {   

echo "<font color=#000000 face=Arial size=2>Please select the type of buildings you want to see on the left.";   

}    

else    

{   

while($row = mysql_fetch_array($result)) { 
echo "<table border=0 width=100% cellspacing=0 cellpadding=0>
        <tr>
          <td width=35% valign=top><p align=left>&nbsp;<A href=urbanca.php?page=building?buildingid=$row[buildingid]><font face=Arial style= 'font-size: 8pt'>$row[name]</B></a></font></b></td>
          <td width=13% valign=top><font face= 'Arial' style= 'font-size: 8pt'><center>$row[height_m] m</font></b></td>
          <td width=13% valign=top><center><font face=Arial style= 'font-size: 8pt'>$row[floors]</font></b></td>
          <td width=13% valign=top><center><font face=Arial style= 'font-size: 8pt'>$row[STATUS]</font></b></td>
          <td width=13% valign=top><center><font face=Arial style= 'font-size: 8pt'>$row[structural_type]</font></b></td>
          <td width=13% valign=top><center><font face=Arial style= 'font-size: 8pt'>$row[year]</font></b></td>
        </tr>
      </table>";   

  } 
}   


}

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM buildings"),0); 

// Figure out the total number of pages. Always round up using ceil() 
$total_pages = ceil($total_results / $max_results); 

// Build Page Number Hyperlinks 
echo "<font face=Verdana size=1 color=000000><br><B>Select a Page</B><br />"; 

// Build Previous Link 
if($page > 1){ 
    $prev = ($page - 1); 
    echo "<a href=\"urbanca.php?pagebuildings?id=".$row["id"]."&page=$prev\"><font face=Verdana size=1 color=000000><B>«</B><Previous</a>&nbsp;"; 
} 

for($i = 1; $i <= $total_pages; $i++){ 
    if(($page) == $i){ 
        echo "$i&nbsp;"; 
        } else { 
            echo "<a href=\"urbanca.php?pagebuildings?id=".$row["id"]."&page=$i\">$i</a>&nbsp;"; 
    } 
} 

// Build Next Link 
if($page < $total_pages){ 
    $next = ($page + 1); 
    echo "<a href=\"urbanca.php?pagebuildings?id=".$row["id"]."&page=$next\">Next&nbsp;<font face=Verdana size=1 color=000000><B>»</B></a>"; 
} 
echo ""; 
?>

[tekky]

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/uc/public_html/buildings.php on line 308

generally this means you had a failed mysql_query() call.... in this case..I would have to say you forgot to DO a mysql_query() call.... you do not have anything setting $result in this file you pasted in.... need to do something like

$result = mysql_query($sql); even before your num_rows calls

note: mysql_fetch_array() requires a result_ID not an SQL statement to function properly so once you add in your query call change that to use the result variable also

[erikhillis]

I seemed to have gotten rid of my error, now something wierd is happening with the results on the page:

http://www.urbancanada.com/urbanca.php?page=buildings&id=1&status=Complete

<?php 

// Database Connection 
$db = mysql_connect("localhost","uc_site","xxxxxxxxxxx");
mysql_select_db (uc_site);

// If current page number, use it 
// if not, set one! 

if(!isset($_GET['page'])){ 
    $page = 1; 
} else { 
    $page = $_GET['page']; 
} 

// Define the number of results per page 
$max_results = 25; 

// Figure out the limit for the query based 
// on the current page number. 
$from = (($page * $max_results) - $max_results); 

// Perform MySQL query on only the current page number's results 

$sql = mysql_query("SELECT * FROM buildings WHERE id='{$_REQUEST['id']}' and STATUS='{$_REQUEST['status']}'"); 
while($row = mysql_fetch_array($sql)) {

if ($max_results < 1) {   

echo "<font color=#000000 face=Arial size=2>Please select the type of buildings you want to see on the left.";   

}    

else    

{   

while($row = mysql_fetch_array($result)) { 
echo "<table border=0 width=100% cellspacing=0 cellpadding=0>
        <tr>
          <td width=35% valign=top><p align=left>&nbsp;<A href=urbanca.php?page=building?buildingid=$row[buildingid]><font face=Arial style= 'font-size: 8pt'>$row[name]</B></a></font></b></td>
          <td width=13% valign=top><font face= 'Arial' style= 'font-size: 8pt'><center>$row[height_m] m</font></b></td>
          <td width=13% valign=top><center><font face=Arial style= 'font-size: 8pt'>$row[floors]</font></b></td>
          <td width=13% valign=top><center><font face=Arial style= 'font-size: 8pt'>$row[STATUS]</font></b></td>
          <td width=13% valign=top><center><font face=Arial style= 'font-size: 8pt'>$row[structural_type]</font></b></td>
          <td width=13% valign=top><center><font face=Arial style= 'font-size: 8pt'>$row[year]</font></b></td>
        </tr>
      </table>";   

  } 
}

// Figure out the total number of results in DB: 
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM buildings"),0); 

// Figure out the total number of pages. Always round up using ceil() 
$total_pages = ceil($total_results / $max_results); 

// Build Page Number Hyperlinks 
echo "<font face=Verdana size=1 color=FFFFFF><br><B>Select a Page</B><br />"; 

// Build Previous Link 
if($page > 1){ 
    $prev = ($page - 1); 
    echo "<a href=\"archive.php?page=$prev\"><font face=Verdana size=1 color=938F7D><B>«</B><Previous</a>&nbsp;"; 
} 

for($i = 1; $i <= $total_pages; $i++){ 
    if(($page) == $i){ 
        echo "$i&nbsp;"; 
        } else { 
            echo "<a href=\"archive.php?page=$i\">$i</a>&nbsp;"; 
    } 
} 

}

// Build Next Link 
if($page < $total_pages){ 
    $next = ($page + 1); 
    echo "<a href=\"archive.php?page=$next\">Next&nbsp;<font face=Verdana size=1 color=938F7D><B>»</B></a>"; 
} 
echo ""; 
?>

[erikhillis]

:rolleyes:

[tekky]

well one thing yo uarent doing is LIMITing the # of results you pull from the query.... and you are using WHERE id=$somevar... thats going to get you a SPECIFIC row only....

the other thing
to COUNT rows returned
simply do $totalnumber = mysql_num_rows($result)

no need to do that mysql_results(mysql_query()) stuff... just redundant and wasteful code that will slow ya down

And I hope for your sake, rolling eyes was a (un)creative way of bumping your thread... cause if you were annoyed with the length of delay between replies, then this will be my last to any threads you start.

[erikhillis]

No no, I was simply bumping the thread up to the top. I guess I'll try and mess with the code for a while until I get it.

[Weedpacket]

Originally posted by erikhillis
No no, I was simply bumping the thread up to the top. I guess I'll try and mess with the code for a while until I get it.

Of course, bumping is itself a good way to get people to ignore the thread in future. If you have to bump, at least report some progress of your own at the same time.