SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot pannick's Avatar
    Join Date
    Aug 2003
    Location
    Florida
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Hey gang a little help please.

    I am rebuilding my website and I did this code with help from a old work buddy who isn't around anymore. I need to have the script seach a table called the_list and then select where state is fl and sort by name and can't seem to get it right.

    I hope that I pasted the code correct and appreciate any help in advance.

    Thanks
    jp

    Code:
    <!-------------------------------------PHP Insert-------------------------------------->
     <?php include("header.inc"); ?>
     
    <?php
    
    /*DVMdb General Business Directory
    
     */
    
     ?>
    
     <?php include("confreg.inc"); ?>
    
      <div align="center">
    	<table border="0" width="60%" cellspacing="0" cellpadding="0">
    		<tr>
    			<td>
    			<p align="center"><b><font face="Verdana">Florida General Listing</font></b></td>
    		</tr>
    	</table>
    	</div>
      <br><tr><td><h4 align="center">&nbsp;</h4></td></tr>
    
    
     <?php
    
     @mysql_connect($host, $user, $password) or die("ERROR--CAN'T CONNECT TO SERVER"); 
    
        @mysql_select_db($database) or die("ERROR--CAN'T CONNECT TO DB"); 
    
    
    
    
    
        $limit          = 15;                
    
        $query_count    = "SELECT count(*) FROM the_listing";     
    
        $result_count   = mysql_query($query_count);  
    
        $totalrows=mysql_fetch_row($result_count);   
    
        $totalrows      = $totalrows[0]; 
    
    
    
        if(empty($page)){ 
    
            $page = 1; 
    
        }
    
             
    
    
    
        $limitvalue = $page * $limit - ($limit); 
    
        $query  = "SELECT * FROM the_listing ORDER BY name LIMIT $limitvalue, $limit";         
    
        $result = mysql_query($query) or die("Error: " . mysql_error()); 
    
    
    
        if(mysql_num_rows($result) == 0){ 
    
            echo("Nothing to Display!"); 
    
        } 
    
    
    
        echo("<table align='center' cellspacing='1' border='0' cellpadding='2' width='60%'>"); 
    
         
    
        while($row = mysql_fetch_array($result)){ 
    
    
    
        echo("<tr style='font-weight=normal;font-size=9pt;color=000000;background-color=FFFFFF'><td>");
    
        if ($row["url"]=="")
    
        {
    
        	$name=$row["name"];
    
        }else 
    
        {
    
        	$name="<a href='http://".$row["url"]."' target='_blank'>".$row["name"]."</a>";
    
        }
    
        echo($name."<br>"); 
    
        echo($row["address"]." • "); 
    
        echo($row["city"].", ".$row["state"]." ".$row["zip"]."<br>"); 
    
        echo($row["phone"]); 
    
        echo("</td></tr><tr><td style='background-color=#0099CC'></td></tr>"); 
    
        } 
    
    
    
        echo("</table>"); 
    
    	
    
        echo "<table><tr><td style='font-size=9pt;'>";
    
        if($page != 1){ 
    
            $pageprev = $page-1; 
    
             
    
            echo("<a href=\"$PHP_SELF?page=$pageprev\">PREV".$limit."</a>&nbsp;"); 
    
        }else{ 
    
            echo("PREV".$limit."&nbsp;"); 
    
        } 
    
    
    
        $numofpages = $totalrows / $limit; 
    
         
    
        for($i = 1; $i <= $numofpages; $i++){ 
    
            if($i == $page){ 
    
                echo($i."&nbsp;"); 
    
            }else{ 
    
                echo("<a href=\"$PHP_SELF?page=$i\">$i</a>&nbsp;"); 
    
            } 
    
        } 
    
    
    
    
    
        if(($totalrows % $limit) != 0){ 
    
            if($i == $page){ 
    
                echo($i."&nbsp;"); 
    
            }else{ 
    
                echo("<a href=\"$PHP_SELF?page=$i\">$i</a>&nbsp;"); 
    
            } 
    
        } 
    
    
    
        if(($totalrows - ($limit * $page)) > 0){ 
    
            $pagenext = $page+1; 
    
              
    
            echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT".$limit."</a>"); 
    
        }else{ 
    
            echo("NEXT".$limit); 
    
        } 
    
         
    
        echo "</td></tr></table>";
    
        mysql_free_result($result); 
    
    
    
    ?>
    <!----------------------------------PHP Insert End------------------------------------>
    <?php include("footer.inc"); ?>

  2. #2
    Avid Logophile silver trophy
    ParkinT's Avatar
    Join Date
    May 2006
    Location
    Central Florida
    Posts
    2,345
    Mentioned
    192 Post(s)
    Tagged
    5 Thread(s)
    Currently, the line:
    Code:
    SELECT * FROM the_listing ORDER BY name LIMIT $limitvalue, $limit"
    gets all records from a table called 'the_listing' and sorts them by the "name" field. If you add a WHERE clause to that query, it will ONLY return those from Florida.
    Like this:
    Code:
    SELECT * FROM the_listing ORDER BY name WHERE state = 'fl' LIMIT $limitvalue, $limit"
    Another way to do this is to use PHP to sort/parse the results, in the variable "$row".
    Hope this helps.
    Don't be yourself. Be someone a little nicer. -Mignon McLaughlin, journalist and author (1913-1983)


    Git is for EVERYONE
    Literally, the best app for readers.
    Make Your P@ssw0rd Secure
    Leveraging SubDomains

  3. #3
    SitePoint Zealot pannick's Avatar
    Join Date
    Aug 2003
    Location
    Florida
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I replaced with your suggestion and get his error

    Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE state = 'fl' LIMIT 0, 15' at line 1

  4. #4
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    what did you replace the line:
    PHP Code:
    $query  "SELECT * FROM the_listing ORDER BY name LIMIT $limitvalue$limit"
    with?
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  5. #5
    SitePoint Zealot pannick's Avatar
    Join Date
    Aug 2003
    Location
    Florida
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $query = "SELECT * FROM vet_list ORDER BY name WHERE state = 'fl' LIMIT $limitvalue, $limit";

  6. #6
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi.
    ORDER BY should come after the WHERE.
    PHP Code:
    $query "SELECT * FROM vet_list WHERE state = 'fl' ORDER BY name LIMIT $limitvalue$limit"
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  7. #7
    Avid Logophile silver trophy
    ParkinT's Avatar
    Join Date
    May 2006
    Location
    Central Florida
    Posts
    2,345
    Mentioned
    192 Post(s)
    Tagged
    5 Thread(s)
    Sorry, pannick.
    I simply did a quick cut-n-paste.

    Thanks, Jake.
    Don't be yourself. Be someone a little nicer. -Mignon McLaughlin, journalist and author (1913-1983)


    Git is for EVERYONE
    Literally, the best app for readers.
    Make Your P@ssw0rd Secure
    Leveraging SubDomains

  8. #8
    SitePoint Zealot pannick's Avatar
    Join Date
    Aug 2003
    Location
    Florida
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks gang that worked.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •