Php/mysql Script Question

[!!blue]

 

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

[TCH-RobertM]

Hi Blue can you give me a Description of the database table

what the column names are and the order they are in ?

[!!blue]

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

[TCH-RobertM]

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

[!!blue]

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

 

[TCH-RobertM]

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";
?>

[!!blue]

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.

[TCH-RobertM]

I am creating a database now to test that

sorry

give me a few minutes to tweak the code

 

 

 

LOL one thing though it did only print Career once

[!!blue]

LOL one thing though it did only print Career once 

<{POST_SNAPBACK}>

LOL, that's a start. Thank you!

[TCH-RobertM]

Blue I have tried a number of ways and have not come up with a solution

I posted a question to a php mysql forum I am on and will post there replys as I get them...

Sorry I could not help yet.

[TCH-RobertM]

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

[TCH-Don]

If this does not work, there are some great link managers from hotscripts

I just converted my link page over to one called Linkster

and I love it.

You may want to look at it

if only to see how they do it.