some code to retrieve info from DB

[Noctagon]

Hello Everyone.

 

I am looking for some assistance to generate a query/code to help me manage my various projects.  The code needs to do the following once I have connected to my database (consider this step already complete):

 

Get the following info out of the active database from the "projects" table. In the projects table I have 4 columns (well, 5 if you count my primary auto inc key).  I need to extract col1 (this is in the form of http://www.mydomain.com/projects/1), col2 (this is a description of the project), col3 (this is also a hyperlink) with a limit of 20 and ordered by col4 (which is in the form of a date)

 

Present the result of the query in a html table where each new cell represents 1 of the maximum 20 instances.  So the first cell of the table will have a pic in it sourced from col1, the alt text for the pic will be from col2 and col3 will be a hyperlink associated to the pic.

 

I really hope this makes sense.  Please ask any questions for clarity.  I really appreciate any help that can be given.

 

Thanks in advance.

[tinker]

1) What DB are you using (mysql)?

2) Have you got your database connected?

3) Have you created a DB, user, tables and inserted some data?

Then you can get some help on how to construct a 'query', which you can then iterate through and make a table!

[drummer101]

$sql = mysql_query ("SELECT * FROM `projects` ORDER BY col4 LIMIT 20");

echo "<table>";
while ($r = mysql_fetch_array($sql)){
<td>$r['col1']</td> // Picture
<td>$r['col2']</td> // Description (alt text)
<td>$r['col3']</td> // Hyperlink
<td>$r['col4']</td> // Date
}
echo "</table>";

Hope that gets you started in the right direction

 

Edit: You'll have to replace col1, col2 etc with the column headers of your table, but you seem smart enough from your explanation to figure that out

[Noctagon]

1) What DB are you using (mysql)?

2) Have you got your database connected?

3) Have you created a DB, user, tables and inserted some data?

Then you can get some help on how to construct a 'query', which you can then iterate through and make a table!

 

Yes mysql

Yes db connected

Yes for number 3

 

$sql = mysql_query ("SELECT * FROM `projects` ORDER BY col4 LIMIT 20");

echo "<table>";
while ($r = mysql_fetch_array($sql)){
<td>$r['col1']</td> // Picture
<td>$r['col2']</td> // Description (alt text)
<td>$r['col3']</td> // Hyperlink
<td>$r['col4']</td> // Date
}
echo "</table>";

Hope that gets you started in the right direction

 

Edit: You'll have to replace col1, col2 etc with the column headers of your table, but you seem smart enough from your explanation to figure that out

 

I am attempting to make the hyperlink associate to the pic so you click the picture to activate the hyperlink.

Also the alt text will be associated to the pic too.....not in a cell next to it.

When I use your code i get an error like this: Parse error: syntax error, unexpected '<' in /home/mysite/public_html/projects.php on line 15  --> line 15 is: <td>$r['col1']</td> // Picture

 

Thanks.

[rajivgonsalves]

you code should be

 

$sql = mysql_query ("SELECT * FROM `projects` ORDER BY col4 LIMIT 20");

echo "<table>";
while ($r = mysql_fetch_array($sql)){
echo "<tr>";
echo "<td>{$r['col1']}</td>"; // Picture
echo "<td>{$r['col2']}</td>"; // Description (alt text)
echo "<td>{$r['col3']}</td>"; // Hyperlink
echo "<td>{$r['col4']}</td>"; // Date
echo "</tr>";
}
echo "</table>";

[Noctagon]

Based on the code provided I guess I am looking for something like this (just not sure how to syntax it):

 

$sql = mysql_query ("SELECT * FROM `projects` ORDER BY col4 LIMIT 20");

 

echo "<table>";

while ($r = mysql_fetch_array($sql)){

<td><a href="[col3]"><img src="[col1]" width="150" height="150" border="0" longdesc="[col2]" /></a></td>

}

echo "</table>";

 

Thanks

[rajivgonsalves]

yes it should be

$sql = mysql_query ("SELECT * FROM `projects` ORDER BY col4 LIMIT 20");

echo "<table>";
while ($r = mysql_fetch_array($sql)){
echo "<tr><td><a href="{$r['col3']}"><img src="{$r['col1']}" width="150" height="150" border="0" longdesc="{$r['col2']}" /></a></td></tr>
}
echo "</table>";

[Noctagon]

yes it should be

$sql = mysql_query ("SELECT * FROM `projects` ORDER BY col4 LIMIT 20");

echo "<table>";
while ($r = mysql_fetch_array($sql)){
echo "<tr><td><a href="{$r['col3']}"><img src="{$r['col1']}" width="150" height="150" border="0" longdesc="{$r['col2']}" /></a></td></tr>"
}
echo "</table>";

 

Hmm, I put a quotation mark at the end of the long line

 

I get an error which says: Parse error: syntax error, unexpected '{', expecting ',' or ';' in /home/mysite/public_html/projects.php on line 15 where line 15 is --> echo "<tr><td><a href="{$r['col3']}"><img src="{$r['col1']}" width="150" height="150" border="0" longdesc="{$r['col2']}" /></a></td></tr>"

[PHP_PhREEEk]

Looks like some escaping within the echo'd string is needed...

 

<?php
$sql = mysql_query ("SELECT * FROM `projects` ORDER BY `col4` LIMIT 20");

echo "<table>";
while ($r = mysql_fetch_array($sql)){
echo "<tr><td><a href=\"{$r['col3']}\"><img src=\"{$r['col1']}\" width=\"150\" height=\"150\" border=\"0\" longdesc=\"{$r['col2']}\" /></a></td></tr>";
}
echo "</table>";

 

PhREEEk

[Noctagon]

Looks like some escaping within the echo'd string is needed...

 

<?php
$sql = mysql_query ("SELECT * FROM `projects` ORDER BY `col4` LIMIT 20");

echo "<table>";
while ($r = mysql_fetch_array($sql)){
echo "<tr><td><a href=\"{$r['col3']}\"><img src=\"{$r['col1']}\" width=\"150\" height=\"150\" border=\"0\" longdesc=\"{$r['col2']}\" /></a></td></tr>";
}
echo "</table>";

 

PhREEEk

 

Thanks, that does the trick.......ONLY 1 more thing to do now

 

This gives me results that run vertically down the page, I did adjust the code so that for each 'while' it just adds a new cell not a new row.  You guessed it...now it goes horizontally but off my page to the right, lol.  How would i make it add a new row at every six cycles through the 'while'.

 

So I would effectively have something like:

 

$sql = mysql_query ("SELECT * FROM `projects` ORDER BY `col4` LIMIT 20");

 

echo "<table>";

echo "<tr>";

while ($r = mysql_fetch_array($sql)){

echo "<td><a href=\"{$r['col3']}\"><img src=\"{$r['col1']}\" width=\"150\" height=\"150\" border=\"0\" longdesc=\"{$r['col2']}\" /></a></td>";

-------> Now if 6 cells have been added to the row then start a new row <-----------

}

echo "</tr>";

echo "</table>";

[Noctagon]

From what i can conjure up it would look something like this????

 

$sql = mysql_query ("SELECT * FROM `projects` ORDER BY `col4` LIMIT 20");

 

echo "<table>";

goto here (not sure how to syntax it  )

echo "<tr>";

set i = 1

 

while ($r = mysql_fetch_array($sql)){

echo "<td><a href=\"{$r['col3']}\"><img src=\"{$r['col1']}\" width=\"150\" height=\"150\" border=\"0\" longdesc=\"{$r['col2']}\" />[/url]</td>";

 

i = i+1

if i = 6 {

echo"</tr>";

goto the goto here spot 

}

}

echo "</tr>";

echo "</table>";

 

 

Any one with some useful ammendments ???

[rajivgonsalves]

Your code should be something like this...

<?php

$sql = mysql_query ("SELECT * FROM `projects` ORDER BY `col4` LIMIT 20");

echo "<table>";
echo "<tr>";
$i = 0;
while ($r = mysql_fetch_array($sql)){
$i++;
echo "<td><a href=\"{$r['col3']}\"><img src=\"{$r['col1']}\" width=\"150\" height=\"150\" border=\"0\" longdesc=\"{$r['col2']}\" /></a></td>";

if (($i%6)==0) {
	echo"</tr><tr>";
}
}
echo "</tr>";
echo "</table>";

?>