Back To Programming / Scripting / Coding Forum

create links from database table

  • webspace
  • Graduate
  • Graduate
  • User avatar
  • Joined: May 17, 2004
  • Posts: 136
  • Loc: Knappa, Oregon
  • Status: Offline

Post September 25th, 2011, 8:08 pm

I want to create a basic search engine.
I'm having trouble displaying the links the way I want to.

So I have a table with 5 values:
id#
URL
description
title
keywords

I want to display the links in this format:

Code: [ Select ]
<a href="['url']">['title']</a><br /> ['description']<br />['url'] as text<br /><br />


I'm guessing I'm creating arrays within an array with a foreach or while loop but I'm not quite getting understanding how this whole thing works?

I found a few scripts and tutorials and understand about 70% of the stuff but I'm lost for what I actually have to do. I have I some experience with other languages but I'm having some difficulty with this.

If anyone can share something from personal experience that would be greatly appreciated. :idea:

That and I might hook you up some bookmarks or links.
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post September 25th, 2011, 8:08 pm

  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post September 26th, 2011, 12:22 am

Have you tried that or are you new to PHP/MySQL?
PHP Code: [ Select ]
<?php
// Connecting to MySQL... mysql_connect('host','user','password')
mysql_connect('localhost', 'root', 'somepass');
 
// Selecting a database to work in
mysql_select_db('dbname');
 
// The SQL you are working with
$sql = 'SELECT * FROM table';
 
// Setting a resource so we can use so we can communicate with the database
$resource = mysql_query($sql);
 
// Looping through the results (since the result is an array) and printing the results in the desired format
while($result = mysql_fetch_assoc($resource))
{
    echo "<a href=\"{$result['URL']"\">{$result['title']}</a><br /> {$result['description']}<br />{$result['URL']}<br /><br/>";
}
?>
  1. <?php
  2. // Connecting to MySQL... mysql_connect('host','user','password')
  3. mysql_connect('localhost', 'root', 'somepass');
  4.  
  5. // Selecting a database to work in
  6. mysql_select_db('dbname');
  7.  
  8. // The SQL you are working with
  9. $sql = 'SELECT * FROM table';
  10.  
  11. // Setting a resource so we can use so we can communicate with the database
  12. $resource = mysql_query($sql);
  13.  
  14. // Looping through the results (since the result is an array) and printing the results in the desired format
  15. while($result = mysql_fetch_assoc($resource))
  16. {
  17.     echo "<a href=\"{$result['URL']"\">{$result['title']}</a><br /> {$result['description']}<br />{$result['URL']}<br /><br/>";
  18. }
  19. ?>



When you select anything from the database it would be retrieved as an object or array... depends on how you ask for it.

Array
There are different kinds of arrays you can get from the database and it's all based on the type of function you use.

Associative Array - In this kind of array the key would be the fieldname and the value would be the value of the field. Below is an example of an associative array:
PHP Code: [ Select ]
<?php
$associative = array('key' => 'value',
                     'fieldname' => 'fieldvalue',
                     'URL' => 'http://www.google.com');
?>
  1. <?php
  2. $associative = array('key' => 'value',
  3.                      'fieldname' => 'fieldvalue',
  4.                      'URL' => 'http://www.google.com');
  5. ?>


Numeric and/or Associative Array This kind of function can retrieve the information from the database in either associative array, or numeric array, or both if you prefer. By default, it would be both. Below is a usage example and an example of a result using this function. (I explain the code in the comments, so read the comments in there).
PHP Code: [ Select ]
<?php
// We only want a numeric array
foreach($result = mysql_fetch_array($resource, MYSQL_NUM))
{
    echo $result[2];
    /*
        array(1 => 'field1value',
              2 => 'field2value');
    */
}
 
// We only want associative array
foreach($result = mysql_fetch_array($resource, MYSQL_ASSOC))
{
    echo $result['URL'];
    /*
        array('id' => 'fieldidvalue',
              'URL' => 'fieldURLvalue');
    */
}
 
// Now we want both types of array
foreach($result = mysql_fetch_array($resource)) // mysql_fetch_array($resource, MYSQL_BOTH)   <-- That would be the same thing
{
    echo $result['URL'] . '<br />';
    echo $result[2];
    /*
        array('id' => 'fieldidvalue',
              1 => 'fieldidvalue',     // Same as the 'id'... just the key is numeric
              'URL' => 'fieldURLvalue',
              2 => 'fieldURLvalue');   // Same as the 'URL'... just the key is numeric
    */
}
?>
  1. <?php
  2. // We only want a numeric array
  3. foreach($result = mysql_fetch_array($resource, MYSQL_NUM))
  4. {
  5.     echo $result[2];
  6.     /*
  7.         array(1 => 'field1value',
  8.               2 => 'field2value');
  9.     */
  10. }
  11.  
  12. // We only want associative array
  13. foreach($result = mysql_fetch_array($resource, MYSQL_ASSOC))
  14. {
  15.     echo $result['URL'];
  16.     /*
  17.         array('id' => 'fieldidvalue',
  18.               'URL' => 'fieldURLvalue');
  19.     */
  20. }
  21.  
  22. // Now we want both types of array
  23. foreach($result = mysql_fetch_array($resource)) // mysql_fetch_array($resource, MYSQL_BOTH)   <-- That would be the same thing
  24. {
  25.     echo $result['URL'] . '<br />';
  26.     echo $result[2];
  27.     /*
  28.         array('id' => 'fieldidvalue',
  29.               1 => 'fieldidvalue',     // Same as the 'id'... just the key is numeric
  30.               'URL' => 'fieldURLvalue',
  31.               2 => 'fieldURLvalue');   // Same as the 'URL'... just the key is numeric
  32.     */
  33. }
  34. ?>


Numeric Array - Retrieves the information from the database in a numerical array.
PHP Code: [ Select ]
<?php
$numeric = array(1 => 'value',
                 2 => 'fieldvalue',
                 3 => 'http://www.google.com');
?>
  1. <?php
  2. $numeric = array(1 => 'value',
  3.                  2 => 'fieldvalue',
  4.                  3 => 'http://www.google.com');
  5. ?>

Not sure why they made three different ones when the middle one can do both of them...

Object
You can also retrieve data from the database as an object instead of an array.

mysql_fetch_object - "Returns an object with properties that correspond to the fetched row..." (http://www.php.net).
PHP Code: [ Select ]
<?php
foreach($result = mysql_fetch_object($resource))
{
    echo $result->URL . <br />;
    echo $result->description;
}
?>
  1. <?php
  2. foreach($result = mysql_fetch_object($resource))
  3. {
  4.     echo $result->URL . <br />;
  5.     echo $result->description;
  6. }
  7. ?>

Objects are a little different then arrays and they are used differently too, as you can see in the example above. instead of...

$result['description'];

... we do ...

$result->description;


Hope that this have helped you understand how this thing works a little better :)
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • webspace
  • Graduate
  • Graduate
  • User avatar
  • Joined: May 17, 2004
  • Posts: 136
  • Loc: Knappa, Oregon
  • Status: Offline

Post September 26th, 2011, 7:30 pm

Thanks for the your help.

I'm going to work on this for a bit. There's a few things there I haven't considered. Still clear as mud but I think I will eventually get this. I'm reading through the tutorials on the PHP website and doing a bunch of video tutorials that are available on YouTube. Maybe I'm just not quite there with the language and logic of PHP.
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post September 26th, 2011, 8:58 pm

You can try reading on one of my tutorials on PHP... I've gotten some pretty good reviews on it. I'm about to re-post the other parts of that tutorial... not sure where they went from this site, but I have the copies saved on my computer somewhere.



// I figured out what happened to the other parts of my tutorial... there was a point where I completely redone the first part and because of that, the other parts no longer made sense... so I removed them. I have to completely redo the other parts.
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • webspace
  • Graduate
  • Graduate
  • User avatar
  • Joined: May 17, 2004
  • Posts: 136
  • Loc: Knappa, Oregon
  • Status: Offline

Post September 27th, 2011, 7:03 am

Cool.

Page 1 of 1

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 175 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.