Natural key, str_replace need help - basic

[computerbarry]

Hi all, recently had a thread discussing surrogate and natural keys in the database. Anyway I've got to the point now were I'm using a natural key and querying my database by 'venue name' and using str_replace, but my problem is I dont know how to switch it back to display correctly on my page?

I have two bit's of code:

Show venue and details

<?php
    require_once('includes/mysql_connect.inc.php');

    
    $sql = "SELECT venue,comments,address FROM venues WHERE venue=" . mysql_real_escape_string($_GET['venue']);
    $result = @mysql_query($sql) or die('Error: ' . mysql_error());
    while ($row = mysql_fetch_array ($result)) {
    echo '<h5>' . $row['venue'] . '</h5><h4><strong>' . $row['comments'] . '</strong></h4><p></p><h3>Location: ' . $row['address'] .'</h3>';
    }
?>

Show venue links

<?php
 
    $query = "SELECT venue FROM venues WHERE category=1";
    $res = mysql_query($query);
    while($row = mysql_fetch_assoc($res))
    echo "<a href=\"venue/".str_replace(" ", "-", $row['venue'])."\">{$row['venue']}</a><br>\n";
    ?>

.str_replace(" ", "-" was added so i can query my table and have the venue names display in my address bar for seo reasons, but how do I run that back through str_replace to make the '-' all be ' '??

making sense? thanks in advance!

[byron3@earthlink]

$venu_with_hypens = str_replace(" ", "-", $row['venue']);
$venu_with_spaces = str_replace("-", " ", $venu_with_hyphens); 

[computerbarry]

Thanks, but how do I fit that into my code?

[byron3@earthlink]

Which snippet of code do you want the venus with no hyphens?

[computerbarry]

lam thinking the first bit of code, can I just point out aswell; if i click a link with just a single venue name and no hyphen it still dosent show and says Error: Unknown column 'somevenue' in 'where clause'

I did try:

<?php
    require_once('includes/mysql_connect.inc.php');
    
    $sql = "SELECT venue,comments,address FROM venues WHERE venue=" . mysql_real_escape_string($_GET['venue']);
    $result = @mysql_query($sql) or die('Error: ' . mysql_error());
    while ($row = mysql_fetch_array ($result)) {
    echo '<h5>'.str_replace("_", " " . $row['venue'] ). '</h5><h4><strong>' . $row['comments'] . '</strong></h4><p></p><h3>Location: ' . $row['address'] .'</h3>';
    }
?>

don't no if that was right? didn't seem to work anyway?

Thanks

[byron3@earthlink]

$venu_with_hypens = mysql_real_escape_string($_GET['venue']);
$venu = str_replace("-", " ", $venu_with_hyphens);

$sql = "SELECT venue,comments,address FROM venues WHERE venue='" . $venu. "'" ; 

[computerbarry]

Did you refresh the page I changed my last post?

I added what you said and instead of Error: Unknown column 'somevenue' in 'where clause' the page is just blank?

My code now:

<?php
    require_once('includes/mysql_connect.inc.php');
    
$venu_with_hypens = mysql_real_escape_string($_GET['venue']);
$venu = str_replace("-", " ", $venu_with_hyphens);
$sql = "SELECT venue,comments,address FROM venues WHERE venue='" . $venu . "'" ; 
    $result = @mysql_query($sql) or die('Error: ' . mysql_error());
    while ($row = mysql_fetch_array ($result)) {
    echo '<h5>' . $row['venue'] . '</h5><h4><strong>' . $row['comments'] . '</strong></h4><p></p><h3>Location: ' . $row['address'] .'</h3>';
    }
?>

cheers

[byron3@earthlink]

Your hypertext link needs to be in the form of :
href="venues.php?venue='" .str_replace("_", " " . $row['venue'] ). "'"

echo your $query out, is it a valid query? Will it run in MySQL admin?

[computerbarry]

Sorry i didn't mention i have .htaccess running with:

RewriteRule ^/?venue/([a-zA-Z_0-9]+)$ venue.php?venue=$1 [L]

if you see what i mean the venues.php is venue.php

Thanks

[byron3@earthlink]

The question mark tells the parser there are name = value pairs after your URL...

What does : print_r( $_GET ) return?

[computerbarry]

The code below works and selects the right links when i roll over them, but no content shows when i click them, thats why I need to add .str_replace("_", " " to the other piece of code which will display the venue and it's details, but where do i put it?

$query = "SELECT venue FROM venues WHERE category=1";
    $res = mysql_query($query);
    while($row = mysql_fetch_assoc($res))
    echo "<a href=\"venue/".str_replace(" ", "_", $row['venue'])."\">{$row['venue']}</a><br>\n"; 

thanks

Anybody help me on this?

I need to add .str_replace("_", " " some where below? yes?

<?php

    require_once('includes/mysql_connect.inc.php');

    $sql = "SELECT venue,comments,address FROM venues WHERE venue=" . mysql_real_escape_string($_GET['venue']);

    $result = @mysql_query($sql) or die('Error: ' . mysql_error());

    while ($row = mysql_fetch_array ($result)) {

    echo '<h5>' . $row['venue'] . '</h5><h4><strong>' . $row['comments'] . '</strong></h4><p></p><h3>Location: ' . $row['address'] .'</h3>';

    }

?>

Thanks

[byron3@earthlink]

Look @ post #6 in this thread I gave you a snippet of code to use in your first codebox.

Try printing out the get variables right after the include statement...

[computerbarry]

ok it prints - Array ( [venue] => venuename )

Using this code :

require_once('includes/mysql_connect.inc.php');
    print_r( $_GET );
$venu_with_hypens = mysql_real_escape_string($_GET['venue']);
$venu = str_replace("-", " ", $venu_with_hyphens);
$sql = "SELECT venue,comments,address FROM venues WHERE venue='" . $venu . "'" ; 
    $result = @mysql_query($sql) or die('Error: ' . mysql_error());
    while ($row = mysql_fetch_array ($result)) {
    echo '<h5>' . $row['venue'] . '</h5><h4><strong>' . $row['comments'] . '</strong></h4><p></p><h3>Location: ' . $row['address'] .'</h3>';
    } 

[byron3@earthlink]

Works to that point, what does your query look like?
echo $query..

[computerbarry]

? were do i put it? thanks again

echo $query; - just like that?

cheers

[byron3@earthlink]

<?php
require_once('includes/mysql_connect.inc.php');

$venu_with_hypens = mysql_real_escape_string($_GET['venue']);
$venu = str_replace("-", " ", $venu_with_hyphens);

$sql = "SELECT venue,comments,address FROM venues WHERE venue='" . $venu. "'" ;

echo $query;
/* Will this query run in MySQL admin ? */
/* Have you opened your database before here ? */
$result = @mysql_query($sql) or die('Error: ' . mysql_error());
while ($row = mysql_fetch_array ($result)) {
    echo '<h5>' . $row['venue'] . '</h5><h4><strong>' . $row['comments'] .
'</strong></h4><p></p><h3>Location: ' . $row['address'] .'</h3>';
    }

[computerbarry]

Sorry but im not quite sure what you mean, copy and past all the code you've just posted in mysql admin including the php tag, if yes it dosent seem to like it and gives an error

thanks again byron

if it help before i started using venue i was usin id which was calling the venues by number and everything worked it's now that ive changed everything and added the str_replace that iam getting the errors

cheers

also if i upload the above code to the server the page is just blank when i click the links, no venues will show

[computerbarry]

If anyone can help?

What ive done you can see from the above posts, ive stopped using my surrgate key id and im using my natural key venue as my main query, can anybody help me on this?

address bar - ../venue/some_venue
web page - <h5>some venue</h5>

still learning here, thanks