Getting contents from a (MYSQL) database and displaying on php page

Forum Moderators: coopster

Message Too Old, No Replies

Getting contents from a (MYSQL) database and displaying on php page

I want that when the user goes to a certain URL (like http://yoursite.com/

psl101

8:13 pm on Mar 24, 2012 (gmt 0)

Hello, I am making a site where users are able to search for a song, and then clicking on it will take you to a page where you are presented artist information, song name, year, genre, as well as a player on the page (which also uses content from the database. I have already done the search engine part, but what i need help on is for me the toughest part. I want that when the user goes to a certain URL (like [yoursite.com...] Here") with a variable in the url so that it changes the content based on the database. I want to have ONE page where it will display contents from a db. For example: Artist - $artist <br> Year - $yr

I know little about php/mysql. The thing that i dont know how to do is getting ONE page to change with a variable in the url.

The table in the database is like this
TABLE - SONGS
Columns - ID, TITLE, ARTIST, YEAR, DURATION, GENRE, KEYWORDS (for the search), and URL.

nyteshade

8:41 pm on Mar 24, 2012 (gmt 0)

If I understand the question then you may want to use the 'post' method when accessing the database. When using the 'get' method, your potential db query fields are exposed in the url. If you want to return to the same page you may try:

<form method='post' action='".$_SERVER["PHP_SELF"]."'>

rocknbil

6:38 pm on Mar 26, 2012 (gmt 0)

You're basically describing every dynamic script out there. :-) You change the page output based on the URL. Example,

yoursite.com/song.php?song=1234

So the get parameter will have a key named "song" and it's value will (or SHOULD) be a number or some other numeric token by which you'll look it up in your database.

This part's pretty important - it's how databases/websites are sometimes hacked - make sure that incoming get parameter is a number, and nothing but a number.

if (isset($_GET['song'] and is_numeric($_GET['song']) and ($_GET['song'] > 0)) {
$song_id = $_GET['song'];
}
else { die("Invalid input"); }

Once you have $song_id, you can look it out in the database and display it any way you want:

$output=null;
$query = "select * from table where id=$song_id;
$result = mysql_query($query) or die ("Error attempting query");


if ($row = mysql_fetch_array($result)) { 
 $output = " 
 Name: " . $row['song_name'] . 
 Artist: " . $row['artist_name'] . 
 Year: " . $row['pub_year']; 
} 
if ($output) { echo $output; } 
else { echo "That is not a valid record on our database"; }

So . . . every ID you "feed" to it you can display different content, from one script.

Take this one step further, eventually you will want to get rid of non SEO-friendly query strings and use SEO-friendly URL's in combination with mod_rewrite - but that's a topic for another day.

psl101

9:51 pm on Mar 26, 2012 (gmt 0)

rocknboil

Thanks a lot! I think this one might "work! Im just getting one error one the

if (isset($_GET['id'] and is_numeric($_GET['id']) and ($_GET['id'] > 0)) { 
$song_id = $_GET['id']; 
} 
else { die("Invalid input"); }

part. It returns "Parse error: syntax error, unexpected T_LOGICAL_AND, expecting ',' or ')' in /home/iphonehe/public_html/data/mf/ns.php on line 30" when i try to access the page. (line 30 is the code that i posted above).

In case you need it, here is ns.php

<html>
<head>
<title>Song Page</title>
<link rel="stylesheet" type="text/css" href="style.css" />
</head>
<body>
<div class="pic"></div>
<?php
 
 //connect to our database
 mysql_connect("localhost","iphonehe_mf","****");
 mysql_select_db("iphonehe_musikfusion");

 //get data
 $title = $runrows['title'];
 $artist = $runrows['artist'];
 $url = $runrows['url'];
 $year = $runrows['year'];
 $duration = $runrows['duration'];
 $genre = $runrows['genre'];
 
if (isset($_GET['id'] and is_numeric($_GET['id']) and ($_GET['id'] > 0)) { 
$song_id = $_GET['id']; 
} 
else { die("Invalid input"); } 

$output=null; 
$query = "select * from table where id=$song_id; 
$result = mysql_query($query) or die ("Error attempting query"); 

if ($row = mysql_fetch_array($result)) { 
 $output = " 
 Name: " . $row['title'] . 
 Artist: " . $row['artist'] . 
 Year: " . $row['year']; 
 Duration: " . $row['duration'];
} 
if ($output) { echo $output; } 
else { echo "That is not a valid record on our database"; } 

</body>
</html>

Edit: Also, in that code, when i go to yoursite.com/ns.php?song_id=1234 will it display the correct things, or does it need more code?

Edit 2: I was able to solve the 1st error by placing a ')' after if (isset($_GET['id'] so now its if (isset($_GET['id']) :D but im getting other errors aswell after this. If you can please revise the code i put above, that would be great. I do not know much about this topic.

psl101

11:39 pm on Mar 26, 2012 (gmt 0)

where it says
$query = "select * from table where id=$song_id;
i already updated TABLE to my actual table, just as a 'by the way'.

The error im getting now is
Parse error: syntax error, unexpected T_STRING in /home/iphonehe/public_html/data/mf/ns.php on line 37

and line 37 is:
$result = mysql_query($query) or die ("Error attempting query");

Again, sorry if im asking a lot. I know very little about MYSQL.

LinusIT

12:44 pm on Mar 27, 2012 (gmt 0)

You've missed a closing quotation mark at the end, it needs to read:

$query = "select * from table where id=$song_id";

rocknbil

4:09 pm on Mar 27, 2012 (gmt 0)

That's correct, if signatures were allowed here mine would say "none of the above is working code and is typed on the fly for example only unless it explicitly says 'tested example.'" The missing first bracket after isset($_GET['id'])

$query = "select * from table where id=$song_id";

This is also correct, with a couple twists you need to know.

The first is that in PHP, scalar $variables will interpolate (output the value they represent) when in double quotes. This is why the above works. if you did this,

$query = 'select * from table where id=$song_id';

it would not work, because the mysql query would error on one level, and do nothing on another: the first is that there is no literal song_id '$song_id' (variables don't interpolate when single quoted) and the second has to do with quoting on the mysql layer.

In the mySQL layer, there is a quoting syntax separate from PHP. The "other reason" the above query works is that numeric values do not need to be quoted in mySQL - in fact, it's a good thing that you don't quote when you search for numeric values. Consider if your variable $song_id is empty or null;

$song_id=null;
$query = "select * from table where id=$song_id";

This gives you, literally,

select * from table where id=

Which will give you a mySQL error (not a PHP error) and alert you to the problem. Now consider if you had quoted that statement. There are many ways to quote within PHP statements, these three are synonymous:

$query = "select * from table where id=\"$song_id\"";
$query = 'select * from table where id="' . $song_id . '"';
$query = "select * from table where id='$song_id'";

So now your literal statement would be

select * from table where id='';

Which would do what?

It wouldn't error.
It wouldn't give you any notice something was wrong.
It just wouldn't work, and you could spend hours wondering why.

Textual values will always need quoting, but when dealing with numeric values it's to your advantage not to quote them.

Returning to your code, what is "runrows?"

$title = $runrows['title'];
(etc)

will it display the correct things, or does it need more code?

Yes it will need more code - I've no idea if those are even your database field names, This assumes the names of the fields in your database are "title", "artist", "year", and "duration" (length of song, at a guess. :-) ):

$output = "
Name: " . $row['title'] .
Artist: " . $row['artist'] .
Year: " . $row['year'];
Duration: " . $row['duration'];

Plus as output those will all run on one line, there's no HTML formatting. The post here is to just give you direction in learing how to obtain your goals, not solve it for you.

psl101

10:25 pm on Mar 27, 2012 (gmt 0)

Thank you for all your help, especially rocknbil. But i have created another thread. The explanation is there.

[webmasterworld.com...]