...

View Full Version : Return row from db



Meltdown
06-08-2004, 09:59 PM
i want to get a row from a mysql db with this code

<?php
mysql_pconnect( "", "", "")
or die( "Unable to connect to SQL server");
mysql_select_db( "") or die( "Unable to select database");

$Artist = $_GET['Artist'];
$Song = $_GET['Song'];

$result = mysql_query('SELECT Lyric FROM Lyrics');
if (!$result) {
die('Invalid query: ' . mysql_error());
}
echo 'Artist: ' . $Artist . '<br>';
echo 'Song: ' . $Song . '<br>';
echo 'Lyrics: ' . $result . '';

?>
i know how to ge tthe artist and song, but i want to get the lyrics from my db by using the artist and the song.. my output for lyrics shows Lyrics: Resource id #2 right now..what does that mean and how do i fix it?

MrShed
06-08-2004, 10:02 PM
$row = mysql_fetch_row($result);


Gives you an array with the contents of 1 row of the results. You are gunna need to put a WHERE clause in your select query to get the correct lyrics as well.

Meltdown
06-09-2004, 02:50 AM
following your instructions to the best of my simple-minded abilities, this is what i have
<?php
mysql_pconnect( "", "", "")
or die( "Unable to connect to SQL server");
mysql_select_db( "") or die( "Unable to select database");


$Artist = $_GET['Artist'];
$Song = $_GET['Song'];
$Lyric = $_GET['Lyric'];
$result = mysql_query('SELECT Lyric FROM Lyrics WHERE Song = ' . $Song . '');
if (!$result) {
die('Invalid query: ' . mysql_error());
}
$row = mysql_fetch_row($result);
echo 'Artist: ' . $Artist . '<br>';
echo 'Song: ' . $Song . '<br>';
echo 'Lyrics: ' . $row . '';

?> and this is what i get
Invalid query: Unknown column 'the song name i typed in' in 'where clause'

Spookster
06-09-2004, 03:45 AM
Is the first letter in your database table column name capitalized? Is it Lyric or is it lyric? First rule of programming, watch capitalization. Most programming languages are case sensitive as well as the Linux box which most sites are being run on. You might be used to capitalizing the first letters of words when you write letters to friends and family but this is not a letter this is programming. In programming there are fairly standard guidelines when it comes to using capital letters. Here's a great little article that discusses that:

http://www.spelman.edu/~anderson/teaching/resources/style/

Meltdown
06-09-2004, 03:51 AM
it's capitlaized..if i changed it, would it fix someo f my problems?

firepages
06-09-2004, 03:59 AM
make that ..



<?
$row = mysql_fetch_row($result);
echo 'Artist: ' . $Artist . '<br>';
echo 'Song: ' . $Song . '<br>';
echo 'Lyrics: ' . $row[0] . ''
?>


though mysql_assoc() may be easier to use in the long run (if not in this particular case) , note how the $row is referenced above and below.



<?
$row = mysql_fetch_assoc($result);
echo 'Artist: ' . $Artist . '<br>';
echo 'Song: ' . $Song . '<br>';
echo 'Lyrics: ' . $row['Lyric'] . ''
?>


if you had fetched all the data from the DB ..


$result = mysql_query('SELECT Lyric,Artist,Song FROM Lyrics WHERE Song = ' . $Song . '');

then you would have $row['Artist'] , $row['Song'] available as well

Meltdown
06-09-2004, 04:25 AM
i hate to keep postin but im lost..ive tried all suggestions so far and this is my code now...

<?php
mysql_pconnect( "", "", "")
or die( "Unable to connect to SQL server");
mysql_select_db( "") or die( "Unable to select database");


$Artist = $_GET['Artist'];
$Song = $_GET['Song'];
$Lyric = $_GET['Lyric'];
$result = mysql_query('SELECT Lyric FROM Lyrics WHERE Song = ' . $Song . '');
if (!$result) {
die('Invalid query: ' . mysql_error());
}

$row = mysql_fetch_row($result);
echo 'Artist: ' . $Artist . '<br>';
echo 'Song: ' . $Song . '<br>';
echo 'Lyrics: ' . $row[0] . ''
?>

and im still getting the same error as mention above in previous posts..believe me when i say i hate to keep bothering everyone because its not like i wanna sit here and try 50 million suggestions from everyon...but dont thake that the wrong way...if it wasnt for suggestions, i wouldn't know anything. So thanks for the help

Spookster
06-09-2004, 04:31 AM
A good way to start debugging problems with database queries is to echo out the query. Usually it is a good idea to store your query in a variable like so. This makes debugging and such easier.



$query = "SELECT Lyric FROM Lyrics WHERE Song = " . $Song;
echo $query;


echo out your query and see what it is being sent to mysql.

Meltdown
06-09-2004, 05:05 AM
well, that fixed one of my problems..letme restate this for better understanding of the situation or for more help... How would I fetch and echo out a row of information that is specified by a user

Meltdown
06-09-2004, 08:03 PM
well, could someone at least give me a script that does this..

Spookster
06-09-2004, 08:16 PM
Well your code is already set up to allow you to do that. You are using $Song as the variable for your where clause and that value is coming from the GET array which I assume you are pulling from a form filled out by the user.

Meltdown
06-09-2004, 08:55 PM
right...and the song and artist values work..i jsut cant get the lyrics to show up. the the song and artist are coming from variables in a url and i was wanting to fethc the lyrics using the song and artist

Spookster
06-09-2004, 09:56 PM
Well then you just need to add another condition to your where clause for the artist. WHERE Song = $Song AND Artist = $Artist

Meltdown
06-09-2004, 11:12 PM
it gives me the same error as mentioned above

Spookster
06-09-2004, 11:55 PM
I mentioned earlier that you should echo your query. What was the output of that?

Meltdown
06-10-2004, 04:30 PM
SELECT Lyric FROM Lyrics WHERE Song = song name i typed in AND Artist = artist name i typed in

Spookster
06-10-2004, 06:14 PM
If that's the output then you are probably running into quotation mark problems. Your values in the where clause need to be quoted as they are strings and not literals.

Meltdown
06-10-2004, 06:54 PM
$result = mysql_query('SELECT Lyric FROM Lyrics WHERE Song = ' . $Song . ' AND Artist = ' . $Artist . ''; is what ihave for the query

Spookster
06-10-2004, 11:36 PM
Ok this is how you should put together your query.




$query = "SELECT Lyric FROM Lyrics WHERE Song = \"" . $Song . "\" AND Artist = \"" . $Artist;



Here I used the escape character \ to tell PHP to treat the next character as a character and not as PHP code. That is how you can intermix quotes inside strings. You can of course always just intermix single and double quotes and not have to use the escape characters in that way but I personally prefer this way so I don't have to keep track of when to use a single or double quote.

Meltdown
06-11-2004, 12:12 AM
that didnt work..it didnt output anything...

<?php
mysql_pconnect( "", "", "")
or die( "Unable to connect to SQL server");
mysql_select_db( "") or die( "Unable to select database");

$Artist = $_GET['Artist'];
$Song = $_GET['Song'];
$Lyric = $_GET['Lyric'];

$result = "SELECT Lyric FROM Lyrics WHERE Song = \"" . $Song . "\" AND Artist = \"" . $Artist;
if (!$result) {
die('Invalid query: ' . mysql_error());
}
$row = mysql_fetch_row($result);
echo 'Artist: ' . $Artist . '<br>';
echo 'Song: ' . $Song . '<br>';
echo 'Lyrics: ' . $row[0] . ''

?>

Spookster
06-11-2004, 05:15 AM
But there were not errors correct? If you got no results then either you don't have any records in your database or what you entered didn't match anything in the database.

Add this in to see if any records are being returned




$records = 0;
$records = mysql_num_rows($result);
echo $records . " records were returned";



Also echo out your query once again and see what it being sent to mysql.

Meltdown
06-29-2004, 08:12 PM
sorry for the long delay. a lot of flooding happened around here and my house kinda got it a little bit so ive been busy. anyway, the records thing didn't output anything except "records were returned" of course.

dumpfi
06-30-2004, 03:10 AM
What about using mysql_query()?

<?php
mysql_pconnect( "", "", "")
or die( "Unable to connect to SQL server");
mysql_select_db( "") or die( "Unable to select database");

$Artist = $_GET['Artist'];
$Song = $_GET['Song'];
$Lyric = $_GET['Lyric'];

$row = mysql_fetch_row(mysql_query("SELECT Lyric FROM Lyrics WHERE Song = '".$Song."' AND Artist = '".$Artist."' LIMIT 1")) or die("Invalid query: ".mysql_error());
echo "Artist: ".$Artist."<br>Song: ".$Song."<br>Lyrics: ".$row[0];
?>

Meltdown
06-30-2004, 04:33 AM
worked wonderfully..thanks man. I really appreciate all of the help provided. I do have one more question.
Of course, lyrics have line breaks. When users submit these lyrics, the line breaks dissappear and the lyrics appear as one big jumbled paragraph. How do I fix that?

dumpfi
06-30-2004, 05:30 AM
Replace this line:

echo "Artist: ".$Artist."<br>Song: ".$Song."<br>Lyrics: ".$row[0];

with this one:

echo "Artist: ".$Artist."<br>Song: ".$Song."<br>Lyrics: ".nl2br($row[0]);

Meltdown
06-30-2004, 05:41 AM
great!...and one last thing. I have a page that lists all of the songs in the database. How do I organize the alphabetically. I've tried several different methods with this and none seem to work.

Spookster
06-30-2004, 06:10 AM
If you want it ordered alphabetically then put an ORDER BY clause in your SQL statement on the column you want it ordered by on.

http://sqlcourse2.com/orderby.html

Meltdown
06-30-2004, 06:25 AM
my fault for not making my request clear enough. I want to display each letter on a seperate page. So, a page for bands with the letter a, a page for bands with the letter b, etc. Sorry for the trouble

Spookster
06-30-2004, 09:50 AM
So use the ORDER BY clause along with a WHERE clause that pulls all artists that start with the letter you are looking for. I assume you are going to put a bunch of links on each page with all the letters of the alphabet linked? Then hardcode the letter into the link and pass it to the query.

http://www.mydomain.com/search.php?letter=a

$letter = $_GET['letter'];

Meltdown
06-30-2004, 04:18 PM
WHERE Artist * A
what would would i put in place of *?

Meltdown
06-30-2004, 04:31 PM
would it be easier to use the sort (http://us2.php.net/manual/en/function.sort.php) array?

dumpfi
06-30-2004, 05:17 PM
<?php
mysql_pconnect( "", "", "")
or die( "Unable to connect to SQL server");
mysql_select_db( "") or die( "Unable to select database");

// write links for all letters
for($i = 65; $i < 91; $i++) {
echo "<a href='name_of_your_php_file.php?letter=".chr($i)."'>".chr($i)."</a> ";
}
?>
<br><br>
<?php

// get the letter the user selected
$letter = $_GET["letter"];

// if a valid letter was selected -> print out the results
if(ord($letter) > 64 && ord($letter) < 91) {
$songs = mysql_query("SELECT Artist, Song FROM Lyrics WHERE Artist LIKE '".$letter."%' ORDER BY Artist");
$curr_artist = "";
while($song = mysql_fetch_row($songs)) {
if($curr_artist != $song[0]) {
echo "Songs by ".$song[0].":<br>";
$curr_artist = $song[0];
}
echo $song[1]."<br>";
}
}
// nothing or no valid letter selected
else {
?>
Select from the links above.
<?php
}
?>

Meltdown
06-30-2004, 09:20 PM
right..thanks..i got that from php.net but how would i add a selection for #'s?

Meltdown
07-02-2004, 12:19 AM
does anyone know how to do this?

Meltdown
07-02-2004, 07:58 PM
well, maybe someone can help me with his one. If i want to display only a certan amount of results on a page and then continue the results to another page, how would i go about doing that?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum