Jump to content

Php/mysql Script Question


!!blue
 Share

Recommended Posts

 

I've created a database of links with URIs, titles, and descriptions. It is uploaded safely to my website via phpMyAdmin.

 

So I want to have a php page show the database similar to this hard-coded page on my blog (I'm making a replacement):

http*//blog.zoblue.com/link-list.php

 

I followed some tutorials online, and after some tweaking, it shows the page like this:

http*//zoblue.com/connect.php

 

Here's the PHP code that outputs the above:

><?php
/* connection info taken out here*/

echo 'connected successfully' . "<br /><br />\n";
$query="SELECT * FROM linklist ORDER BY 2, 4;";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close($link);
echo "<strong>Database Output</strong>\n<br />\n";

$i=0;
while ($i < $num) {
$type=mysql_result($result,$i,"type");
$name=mysql_result($result,$i,"name");
$url=mysql_result($result,$i,"url");
$desc=mysql_result($result,$i,"desc");
echo "<strong>" . $type . "</strong>\n";
echo "<dl><dt><a href='" . $url . "' title='" . $name. "'>" . $name . "</a></dt>\n";
echo "<dd>" . $desc . "</dd></dl>\n\n";
$i++;
}
echo "\n<br><br>\n";
?>

 

but I'd like to group the links by their category (the type column in the DB table). Can anyone help me do this? I can't wait for my PHP and MySQL books to come in the mail; young grasshopper has no patience. ;)

 

thanks,

!!blue

Link to comment
Share on other sites

Ooop! I forgot that:

 

key | type | name | url | desc

 

key = number; auto increment unique id

type = text; the type of link, e.g. 'css sources', 'entertainment', 'music'

name = text; usually the title of the page

url = text; the actual URL of the link

desc = text; a short one or two sentence description of the link

Link to comment
Share on other sites

Hi Blue

 

Ok now that I have that information I have just a few questions for you

the links that you posted

 

//blog.zoblue.com/link-list.php hard coded code

 

and then

 

//zoblue.com/connect.php Actual PHP generated code

 

the links displayed on your hardcoded page reflect the following markup EXAMPLE

><b>Daily Links: </b>
<ul>
<li><a href="http://www.hotmail.com" class="link" target="_blank">Hotmail email</a></li>
<li><a href="http://mail.yahoo.com" class="link" target="_blank">Yahoo email</a></li>
</ul>

 

the new PHP generated creates markup as such

><strong>career</strong>
<dl><dt><a href='http://www.cityofchicago.org/mowd' title='Chicago Office of Workforce Dev'>Chicago Office of Workforce Dev</a></dt>
<dd>helps Chicagoans find and train for jobs; help businesses find workers</dd></dl>

<strong>career</strong>
<dl><dt><a href='http://chicagobusiness.com/' title='Crain's Chicago Business'>Crain's Chicago Business</a></dt>
<dd>Chicago business news, lists, rankings, directory and more</dd></dl>

 

The difference between them is in your hardcoded example you are using

  • within an unordered list element

     

    and the php is generating a definition list

    and a definition description

     

    To change the php to print out the URLs in like you have on your hardcoded page

    you will need to change it to look like this

    >echo 'connected successfully' . "<br /><br />\n";
    $query="SELECT * FROM linklist ORDER BY 2, 4;";
    $result=mysql_query($query);
    $num=mysql_numrows($result);
    mysql_close($link);
    echo "<strong>Database Output</strong>\n<br />\n";
    echo "<ul>\n";
    $i=0;
    while ($i < $num) {
    $type=mysql_result($result,$i,"type");
    $name=mysql_result($result,$i,"name");
    $url=mysql_result($result,$i,"url");
    $desc=mysql_result($result,$i,"desc");
    echo "<strong>" . $type . "</strong>\n";
    echo "<li><a href='" . $url . "' title='" . $name. "'>" . $name . "</a></li>\n";
    $i++;
    }
    echo "</ul>\n";
    ?>

     

    If this is not what you were looking for let me know

    take care

    Robert

    Link to comment
    Share on other sites

    I do want a definition list so that each link has a short description. But the PHP script I'm using loops through the links and spits out each DB table row like this:

    >career
    Chicago Office of Workforce Dev
       helps Chicagoans find and train for jobs; help businesses find workers
    
    career
    Crain's Chicago Business
       Chicago business news, lists, rankings, directory and more
    
    career
    Creative Directory
       resource for the creative community in the Midwest
    .....

     

    but I want the table rows to be shown like this, grouped together by type:

     

    >career
    Chicago Office of Workforce Dev
       helps Chicagoans find and train for jobs; help businesses find workers
    Crain's Chicago Business
       Chicago business news, lists, rankings, directory and more
    Creative Directory
       resource for the creative community in the Midwest
    .....

     

    sorry, I didn't explain visually ;)

     

    thanks,

    !!blue

     

    Link to comment
    Share on other sites

    Hi Blue ,

    Ok I dont have a way to test this but I think this is basically what you are after

    What I did was add an additional while statement to check that if the type has changed

    try this and let me know if you get any errors

     

    *** Edit *** might need to move the inital holdtype above the code

    while ($i

     

    ><?php
    /* connection info taken out here*/
    
    echo 'connected successfully' . "<br /><br />\n";
    $query="SELECT * FROM linklist ORDER BY 2, 4;";
    $result=mysql_query($query);
    $num=mysql_numrows($result);
    mysql_close($link);
    echo "<strong>Database Output</strong>\n<br />\n";
    $i=0;
    while ($i < $num) 
    {
    $type=mysql_result($result,$i,"type");
    $name=mysql_result($result,$i,"name");
    $url=mysql_result($result,$i,"url");
    $desc=mysql_result($result,$i,"desc");
    
    $holdtype=$type;
    echo "<strong>" . $type . "</strong>\n"; /* initialize First Type */
      while ($holdtype = $type)
    	 {
    	 echo "<dl><dt><a href='" . $url . "' title='" . $name. "'>" . $name . "</a></dt>\n";
    	 echo "<dd>" . $desc . "</dd></dl>\n\n";
    	 $i++;
    	 }
    $holdtype = $type;
    echo "<strong>" . $type . "</strong>\n";
    	 echo "<dl><dt><a href='" . $url . "' title='" . $name. "'>" . $name . "</a></dt>\n";
    	 echo "<dd>" . $desc . "</dd></dl>\n\n";
    	 $i++;
    }
    echo "\n<br><br>\n";
    ?>

    Link to comment
    Share on other sites

    eek! that gave me this:

    >career
    Chicago Office of Workforce Dev
       helps Chicagoans find and train for jobs; help businesses find workers
    
    Chicago Office of Workforce Dev
       helps Chicagoans find and train for jobs; help businesses find workers
    
    Chicago Office of Workforce Dev
       helps Chicagoans find and train for jobs; help businesses find workers
    
    Chicago Office of Workforce Dev
       helps Chicagoans find and train for jobs; help businesses find workers
    ....

     

    kinda scary, I had to stop the page from continuing to load. ;)

    Link to comment
    Share on other sites

    Hi Blue I did not get an answer from other board that helped much but I did finally figure it out

    >       echo "connected successfully\n";
    
           echo "<strong>Database Output</strong>\n<br />\n";
    $query=mysql_query("select * from bluetest group by type");
    while($row=mysql_fetch_assoc($query))
    {
    $cat=$row['type'];
     if($cat!=$lastCat) 
     {
     echo "<strong>" . $cat . "</strong>\n";     	 //output Catagory type 
     $lastCat=$cat;  //set lsat type to current type     
     }
    //do the rest of outputing articles
    //second query to retrieve the actual URLs
        $query2 = "SELECT namel,url,descrip FROM bluetest WHERE type='$cat'";
        $result2 = mysql_query($query2) or die(mysql_error());
        while ($row2 =  mysql_fetch_array($result2)) 
        {
        echo "<dl><dt><a href='" . $row2["url"] . "' title='" . $row2["namel"] . "'>" . $row2["namel"] . "</a></dt>\n";
        echo "<dd>" . $row2["descrip"] . "</dd></dl>\n\n";
        } 
        }
        echo "\n<br><br>\n";

     

    you will need to tweek it a little bit but it does work

    here is my output

    ><strong>Database Output</strong>
    <br />
    <strong>career</strong>
    <dl><dt><a href='http://www.salary.com/hom' title='Salary'>Salary</a></dt>
    
    <dd>providing employee compen</dd></dl>
    
    <dl><dt><a href='http://www.techies.com' title='Techies'>Techies</a></dt>
    <dd>job search; company resea</dd></dl>
    
    <strong>cars</strong>
    <dl><dt><a href='http://www.kbb.com/' title='KBB'>KBB</a></dt>
    <dd>Kelley Blue Book; search</dd></dl>
    
    <strong>code sources</strong>
    
    <dl><dt><a href='http://www.actionscript.o' title='Actionscript links'>Actionscript links</a></dt>
    <dd>forum entry with motherlo</dd></dl>
    
    <dl><dt><a href='http://www.blooberry.com/' title='Blooberry HTML'>Blooberry HTML</a></dt>
    <dd>None</dd></dl>

     

    If this does not work please feel free to message me

    Good luck

    Robert

    Link to comment
    Share on other sites

    Join the conversation

    You can post now and register later. If you have an account, sign in now to post with your account.

    Guest
    Reply to this topic...

    ×   Pasted as rich text.   Paste as plain text instead

      Only 75 emoji are allowed.

    ×   Your link has been automatically embedded.   Display as a link instead

    ×   Your previous content has been restored.   Clear editor

    ×   You cannot paste images directly. Upload or insert images from URL.

     Share

    ×
    ×
    • Create New...