MYSQL - extracting info from a cell to append to a url?

damanic

Heloo there,

I've got a problem with the navigation links on my webpage. The navigation links to a page whos url changes. Im new to php so bear with me.

I want to be able to grab a cell from a table in mysql and display it something like this:

echo ("<a href=\"http://www.site.com/body[$insert-from-mysql-columnone-rowone].htm\"><img src=image.gif border=0></a>");

Can anyone point me in the right direction?

toplay

I don't really understand your problem. I mean you've already done it in your original post. Just remove the square brackets, but you have a variable there now that could come from the table.

So, is your question how do I retrieve data from MySQL or what's the exact problem?

FYI - Code example of retrieving data from MySQL:
http://www.phpbuilder.com/board/showpost.php?p=10648003&postcount=4

damanic

Thenks toplay you gettin me somewhere now.

So this is where im up to now. But its wrong. Well at least I think the 'printf' bit is wrong. As I said php is all new to me so im confident I've done something wrong.

<?php

 // Connect to MySQL server first – You can use variables instead of these literals 
$db = mysql_connect('localhost', 'username', 'password'); 
if (!$db) { 
    echo 'Could not connect to MySQL server. <br />Error # ', mysql_errno(), ' Error msg: ', mysql_error(); 
    exit; 
} 

// Select the database you want to use – You can use a variable here too instead 
if (!mysql_select_db('china', $db)) {   // Did selection fail? 
    // Handle error 
    echo 'DB Selection failed. <br />Error # ', mysql_errno(), ' Error msg: ', mysql_error(); 
    exit; 
} 
$category = mysql_escape_string('Teas');  // or use addslashes() 
$sql = "SELECT number FROM navigation WHERE name = '$category'"; 
$result = mysql_query($sql, $db); 
if (!$result) { 
    // Handle error 
    echo 'Query failed. SQL: ', $sql, '<br />Error # ', mysql_errno(), ' Error msg: ', mysql_error(); 
    exit; 
} 

$row = mysql_fetch_assoc($result);    // Try to retrieve the data 
if (!$row)           // No data returned? 
    // Not necessarily an error – Just no data matched search criteria 
    echo 'No data found <br />';  

else      // There is valid data returned 
    printf("<a href=\"http://www.site.com/body$number.htm\"><img src=image.gif border=0></a>");
?>

Im guessing from this

$sql = "SELECT number FROM navigation WHERE name = '$category'";

that

number = name of column
navigation = name of table

and im I right to use $number in this way to diplay the result?

toplay

No, the fetch is assigning it to a $row variable that's an array.

So, change this:

printf("<a href=\"http://www.site.com/body$number.htm\"><img src=image.gif border=0></a>");

to this:

echo '<a href="http://www.site.com/body', $row['number'], '.htm"><img src=image.gif border=0></a>';

hth.

damanic

Wickid 🙂

I'll give it a whirl.

damanic

Wow php is a beautifull thing. I've got it working just as I wanted!

Thanks alot for your help toplay.

One last thing. If use this script for multiple links on the one page do I do it like this:

<?php

 // Connect to MySQL server first – You can use variables instead of these literals 
$db = mysql_connect('localhost', 'username', 'password'); 
if (!$db) { 
    echo 'Could not connect to MySQL server. <br />Error # ', mysql_errno(), ' Error msg: ', mysql_error(); 
    exit; 
} 

// Select the database you want to use – You can use a variable here too instead 
if (!mysql_select_db('china', $db)) {   // Did selection fail? 
    // Handle error 
    echo 'DB Selection failed. <br />Error # ', mysql_errno(), ' Error msg: ', mysql_error(); 
    exit; 
} 
?>

<?php
$category = mysql_escape_string('Teas');  // or use addslashes() 
$sql = "SELECT number FROM navigation WHERE name = '$category'"; 
$result = mysql_query($sql, $db); 
if (!$result) { 
    // Handle error 
    echo 'Query failed. SQL: ', $sql, '<br />Error # ', mysql_errno(), ' Error msg: ', mysql_error(); 
    exit; 
} 

$row = mysql_fetch_assoc($result);    // Try to retrieve the data 
if (!$row)           // No data returned? 
    // Not necessarily an error – Just no data matched search criteria 
    echo 'No data found <br />';  

else      // There is valid data returned 
    printf("<a href=\"http://www.site.com/body$number.htm\"><img src=image.gif border=0></a>");
?>

<?php
$category = mysql_escape_string('Link2);  // or use addslashes() 
$sql = "SELECT number FROM navigation WHERE name = '$category'"; 
$result = mysql_query($sql, $db); 
if (!$result) { 
    // Handle error 
    echo 'Query failed. SQL: ', $sql, '<br />Error # ', mysql_errno(), ' Error msg: ', mysql_error(); 
    exit; 
} 

$row = mysql_fetch_assoc($result);    // Try to retrieve the data 
if (!$row)           // No data returned? 
    // Not necessarily an error – Just no data matched search criteria 
    echo 'No data found <br />';  

else      // There is valid data returned 
    printf("<a href=\"http://www.site.com/body$number.htm\"><img src=image.gif border=0></a>");
?>

toplay

If you want to extract specific data, then specify it in the "where" clause like you're doing, but if you want to retrieve all the data, then leave out the "where" clause altogether.

If you wanted to retrieve both rows, then you can do it with one query like so:

$sql = "SELECT number FROM navigation WHERE name = 'Teas' or name = 'Link2' ";

Please note that printf() requires argument(s) passed to it.

Here's an example if you want to retrieve all rows from the navigation table and you want to use printf():

// Connect and select database first

$sql = 'SELECT number FROM navigation';
$result = mysql_query($sql);
if (!$result) {
    // Handle error
    echo 'Query failed. SQL: ', $sql, '<br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
    exit;
}
// The while loop stops when there's no data left; it might not even go in loop
// and echo anything when there's no data returned on the first call to
// mysql_fetch_assoc()
while($row = mysql_fetch_assoc($result)) {  // Retrieve data until no more
    printf('<a href="http://www.site.com/body%s.htm"><img src=image.gif border=0></a>', $row['number']);
}

🙂

damanic

Awsome!

Ok that last way works best but if I use it im going to have to call up the image for the link from the database aswell. As it stands it would generate the same graphic each time.

Ok heres my guess. This is fun.

$sql = 'SELECT sid and img FROM navigation'; 
$result = mysql_query($sql); 
if (!$result) { 
    // Handle error 
    echo 'Query failed. SQL: ', $sql, '<br />Error # ', mysql_errno(), ' Error msg: ', mysql_error(); 
    exit; 
} 
// The while loop stops when there's no data left; it might not even go in loop 
// and echo anything when there's no data returned on the first call to 
// mysql_fetch_assoc() 
while($row = mysql_fetch_assoc($result)) {  // Retrieve data until no more 
    printf('<a href="http://www.site.com/body%s.htm"><img src=$s border=0></a>', 
	$row['sid'],
	$row['img'],
	); 
}  

?>

Am I close?

toplay

Yes, very close.

Change this:

$sql = 'SELECT sid and img FROM navigation';

To this:

$sql = 'SELECT sid, img FROM navigation';

And change this part:

src=$s

To this:

src=%s

Change this:

$row['img'],

To this:

$row['img']

🙂

damanic

wahoo thats easy peasy.

Ive got it working with rollover and alls good except for one thing. The order in which they are displayed.

Left navigation (Product Range)

gone
compared to how it should be
gone

How could I make it display in the right order. I tried adding a numberd id field to the table but no effect. Am I on the right track?

Thanks alot for your help. This has saved me alot of time and I have gained some usefull knowledge. Why have I been static all this time!?

toplay

Basically, you can have MySQL sort a column by using 'ORDER BY' followed by the column name(s) of what it should sort by. Example:

$sql = 'SELECT sid, img FROM navigation ORDER BY sid ASC';

That would sort by the sid column values from lowest to highest. To sort from highest to lowest, you can replace "ASC" with "DESC".

See the manual page regarding select syntax here:

http://dev.mysql.com/doc/mysql/en/select.html

Happy coding.

damanic

Perfect. I think that wraps it up.

Toplay, i'd still be scratching my arse on this one if it wasnt for your help.

Thank you.