PHP MYSQL while change field name each time
I have a database to store people's quick links. This is a very basic quick link storage method. The database looks like this:
full_name | 1url | 1name | 2url | 2name | 3url |3 name | 4url |4name | 5url | 5name
^This goes on until 10. I know this is a bad way, but this is for an unprofessional website.
I will want to put the result into an ordere开发者_如何学编程d list. But I am unsure how to change the number (1url or 2url) each time?
So currently I have it set up like this in PHP
$result = mysql_query(SELECT * FROM `links` WHERE `full_name`='$loggedin')or die (mysql_error());
while($row = mysql_fetch_array($result)){
echo '<li><a href="';
echo $row['1url'];
echo '"></a></li>';
}
But I am having no luck with that! I'm very unsure of what I should do. I want it to display another <li>
with an <a>
and the link plus name of the link for each row found.
Thanks! Please be specific with me, as this is new ground! :D
EDIT:
I have also run into another problem. I have used code from peoples' answers and most of them work. However, If one of the fields is blank (so a user has only 6 quick links) it still shows an <li>
. Now I can't see anyway round this issue. :/
SOLUTION:
Here is what works:
while($row = mysql_fetch_array($result)){
for($i = 1; $i < 10; $i++) {
if(!trim($row[$i . 'url'])=='') {
echo '<li><a href="';
echo $row[$i . 'url'];
echo '">';
echo $row[$i . 'name'];
echo '</a></li>';
} //end of didn't trim
}//end for for
}//end of while
$result = mysql_query("SELECT * FROM `links` WHERE `full_name`='$loggedin'")or die (mysql_error());
while($row = mysql_fetch_array($result)){
for($i = 1; $i < 10; $i++)
{
echo '<li><a href="';
echo $row[$i . 'url'];
echo '"></a></li>';
}
}
Mind you, this is pretty hacky... I would have just implemented it with 3 columns (maybe 4 using an autoincrement to sort) and then select the rows based on the user, emitting each row. That removes the 10 url limit.
Edit
For your second question, have a look at the PHP 'empty' function and break/continue the loop if the function returns true.
It would be a lot cleaner and easier to change your database setup a little bit. You could have two tables:
users
- id: a unique ID for each user, probably an auto increment int of some sort
- full_name: just as you've used it in your table
quick_links
- id: quick link id, probably an auto increment int (or you could do a primary index on user_id+order)
- user_id: the user ID to tell us who this quick_link belongs to
- name: the name of the quick link
- url: the url of the quick link
- order: what order to show this link in
Then you can simply do something like
$userid_result = mysql_query(
"SELECT `id` from `users` WHERE `full_name` = $loggedin;"
);
$row = mysql_fetch_row($userid_result);
$userid = $row[0];
$links_result = mysql_query(
"SELECT * from `quick_links` WHERE `user_id` = $userid ORDER BY `order` ASC;"
);
while($quick_link = mysql_fetch_object($links_result))
{
printf("<li><a href=\"%s\">%s</a></li>", $quick_link->url, $quick_link->$name);
}
Of course you'd need some error checking in there, but that gives you an idea.
You need to put some double quotes around your select statement:
$result = mysql_query("SELECT * FROM `links` WHERE `full_name`='$loggedin'") or die (mysql_error());
while($row = mysql_fetch_array($result)){
echo '<li><a href="';
echo $row['1url'];
echo '"></a></li>';
}
while ($row = mysql_fetch_array($result))
{
$full_name = array_shift($row);
for ($i = 0; $i < 10; ++$i)
{
echo '<li><a href=' . htmlspecialchars(array_shift($row)) . '>';
echo array_shift($row);
echo '</a></li>';
}
}
array_shift
returns the first element from an array and removes it from the array at the same time. So the code above removes the full_name
field from the record and then iterates over the rest of the record, removing and printing a URL and its corresponding name on each iteration.
htmlspecialchars
is used to ensure that a valid a
-tag is created. Depending on where the name of the link comes from, it should also be used on the name of the link.
You SQL query needs to be passed as a string enclosed in "...":
$result = mysql_query( "SELECT * FROM `links` WHERE `full_name`='$loggedin'" )
or die (mysql_error());
$i = 0;
while($row = mysql_fetch_array($result)){
$attributeURL = $i . 'url';
$attributeName = $i++ . 'name';
echo '<li>'
. '<a href="' . $row[ $attributeURL ] . '">' . $row[ $attributeName ] . </a>'
. '</li>'
;
}
精彩评论