Displaying SQL Results

[Zack2008]

Hi I have made a database with all different things (venue, team names etc), the team names display fine, but I am using different code for venues (counting) and these just won't display at all, I am really new to SQL so don't know what to do to fix it!

The code for getting the SQL is

$sql = 'select venue,count(ven ue) as frequency from matches group by venue limit 300';

which works fine in phpMyAdmin and displays what I want to, but I don't know how to display it on my page! I do have the sql connection info in the php page too, plus the code which is used for displaying all my other pages but doesn't want to work now! I would like a table with headers of Venue & Number if possible, then the venues and number next to them, as it displays in phpMyAdmin

Thanks
Zack :)

[samikhan83]

hi zack ....

be clear.... with ur question
and wat u want to retrive from ur table ......

[Atli]

Hi.

So the problem is that you don't know how to execute the query and display the results in your PHP code?

If so, then you should check out the PHP manual. This topic is well covered in the MySQL section.
Check out the examples for mysql_connect and mysql_query.

[Zack2008]

Thanks, I got it working anyway in the end, part of it didn't copy.

This is part of my code

if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td> ".$row['venue'] . "</td>"; 
echo "<td> ".$row['frequency'] . "</td></tr>"; 
}

I have added a hyperlink just before venue, but as there are spaces in them only the first part is included in the link (e.g. for Old Trafford only "Old" is included, as there are a lot with "The" this won't work as a solution). Is there any way to take the spaces out for the link (or changes them to %20), but not for the actual listing, so the link is OldTrafford and the display is Old Trafford? That way I can make a new window open with the team names in. I would like a Javascript style box when you highlight over the text (like this forum for adverts) but they don't seem to let each link be customised. I used something I did before but it doesn't seem to work

Thanks
Zack

[Atli]

You can use urlencode to make sure your text will pass safely through the URL.
[code=php]
$text = "Old Trafford"
$url = urlencode($text );

echo '<a href="details.p hp?v="'. $url .'">'. $text .'</a>';
[/code]
Which should link to "details.php?v= Old%20Trafford" .

On the receiving side, however, fetching the value through the $_GET super-global should return the original version with the space.

[Zack2008]

Thanks, I thought I was doing this right, but obviously not!

I'm getting "Parse error: syntax error, unexpected T_VARIABLE in /home/updates/public_html/matches/grounds.php on line 16"

If I make the "" into just " then line 15 has an error instead!

<div align=\"left\"><table border=\"1\" bordercolorlight=\"#000000\" bordercolordark=\"#000000\"><tr><td>Venue</td><td>Times Visited</td></tr>
<?php
mysql_connect("localhost", "", "") or die(mysql_error()); 
mysql_select_db("") or die(mysql_error()); 
$query = sprintf("select venue,count(venue) as frequency from matches group by venue ORDER BY `frequency` DESC limit 300");
$result = mysql_query($query);

if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

while ($row = mysql_fetch_assoc($result)) {
$text = "".$row['venue'] . ""
$url = urlencode($text);
echo "<tr><td>echo '<a href="details.php?v="'. $url .'">".$row['venue'] . "</a>';</td>"; 
echo "<td> ".$row['frequency'] . "</td></tr>"; 
}

mysql_free_result($result);
?>
</table></div>

[Atli]

Line 15 is missing the end semi-colon.
And you don't need the "". $var .""; thing.
[code=php]
// This
$var = "". $var1 ."";
// And this
$var = "$var1";

// Works exactly like
$var = $var1;
[/code]
The reason I used it is because I was adding the variable to a single-quoted string.

And line 17 is all messed up. Your quotes don't add up, you have an echo command inside the string, and there is an extra </td> after you close the line.

[Zack2008]

Thanks I missed that with line 15 that works now, just line 17 causing problems. It was partly what I copied off here. I am using dreamweaver trail which is useful for highlighting when things go wrong (unless Cpanel where it just looks like one big mess), but I can't get 17 to work, have tried lots of different ways and just get

Parse error: syntax error, unexpected T_STRING, expecting ',' or ';' in /home/updates/public_html/matches/grounds.php on line 17

I don't see what's wrong with this, nor does dreamweaver :s

echo "<tr><td><a href="details.php?v='. $url .'>".$row['venue'] ."</a></td>";

after all this I have probably made some kind of mistake which means I won't be able to have each venue linked differently using this code anyway! As you can tell I haven't got a clue with php!

Zack

[Atli]

The problem there are all the mismatching quote-tags.

If you open a string using a double-quote, PHP will close the string on the next double-quote. Same for single-quotes.

Consider this:
[code=php]
$var = "<a href="page.php" >Linkage</a>";
[/code]
You see the problem there?
PHP will open a string on the first double-quote, but then close it on the second, which is meant to be a part of the string, not the end of it.
So, the following text (page.php"...) will be executes as PHP code, which will fail with a parse error.

If you want to use double-quotes inside a string, the string must either start and end with a single-quote, or the extra quotes need to be escaped.
Like so:
[code=php]
$var = '<a href="page.php" >Linkage</a>';
$var = "<a href=\"page.php \">Linkage</a>";
[/code]
Both of these will work.

Now, when you add variables, like you do with your line, you need to close the string and add it using a dot, and then open it again. Like:
[code=php]
$var = '<a href="'. $url .'">'. $label .'</url>';
[/code]
Here the double-quotes are a part of the string, and all the single-quotes either close or open a string.

You can inject variables directly into a double-quote string, but lets no complicate the matter further.

[Zack2008]

Thanks, understand that but have never really understood other examples so have just messed about until it's worked!

Thanks it's all working now, just one other thing but I will start a new topic when I get round to doing it, bit too much to tag onto this topic