CODEWITHSUNDEEP
phpmysql
ryryan
ryryan

Reputation: 3928

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 ordered 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

Upvotes: 0

Views: 404

Answers (5)

Joanne C
Joanne C

Reputation: 1115

$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.

Upvotes: 1

Matthew
Matthew

Reputation: 685

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.

Upvotes: 1

SteAp
SteAp

Reputation: 11999

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>'
        ;            


}

Upvotes: 0

Oswald
Oswald

Reputation: 31655

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.

Upvotes: 0

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120937

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>';            
   }

Upvotes: 0

Related Questions