PHP/mySQL code works with text but not with variable

Question

gazza7 0 Newbie Poster

14 Years Ago

Hi

I'm a newbie to PHP and mySQL I have the following problem.

I have a database with mp3's which I query by artist name, I then print the results in a table with a maximum of 15 rows. I can then have a next, prev, last and first button to navigate. I also have a page with a form to submit the query and this puts it into a variable and into the query. The first page works fine, but if I go to the next page nothing shows, even though it says their are 21 pages. Their are two queries on the page.

$query = "SELECT * FROM mp3s WHERE artist='$artist'Order by album" .
" LIMIT $offset, $rowsPerPage";

$query   = "SELECT COUNT(*) AS numrows FROM mp3s  WHERE artist='$artist'Order by album";

If I replace the variable $artist in the query with text I eg. U2, the page shows and if I click on next the next page will show with the results.

$query = "SELECT * FROM mp3s WHERE artist='U2'Order by album" .

         " LIMIT $offset, $rowsPerPage";

$query   = "SELECT COUNT(*) AS numrows FROM mp3s  WHERE artist='U2'Order by album";

Here is the full code:-

<?php include 'Connections/mp3.php';?>


<?php



$bgcolor='#f1f122';

echo "<html>";

echo "<head>";

echo "</head>";



echo "<body bgcolor='$bgcolor'>";








 $artist = $_POST['search'];

?>






<table width="800" border="1" cellspacing="1" cellpadding="1">

<tr>

<th><font face="Arial, Helvetica, sans-serif">Artist</font></th>

<th><font face="Arial, Helvetica, sans-serif">Album</font></th>

<th><font face="Arial, Helvetica, sans-serif">Song Title</font></th>

<th><font face="Arial, Helvetica, sans-serif">File Path</font></th>

</tr>





<?php

// how many rows to show per page

$rowsPerPage = 15;



// by default we show first page

$pageNum = 1;



// if $_GET['page'] defined, use it as page number

if(isset($_GET['page']))

{

    $pageNum = $_GET['page']; 

}



// counting the offset

$offset = ($pageNum - 1) * $rowsPerPage; 





@mysql_select_db($database_mp3) or die( "Unable to select database");

$query = "SELECT * FROM mp3s WHERE artist='$artist'Order by album" .

         " LIMIT $offset, $rowsPerPage";

$result = mysql_query($query) or die('Error, query failed');

// of get number of pages


// print the random numbers

while($row = mysql_fetch_array($result))

{

  

$f1=$row['artist'];

$f2=$row ['album'];

$f3=$row ['title'];

$f4=$row ['filepath'];



$trimmed = trim($f4, " /var/www/");



$f4 =$trimmed;


?>

<tr>

<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>

<td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>

<td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>

<td><font face="Arial, Helvetica, sans-serif"><a href=" <?php echo $f4; ?>">play</a></font></td>

</tr





<?php



$row++;

}



?>







<?php

// ... the previous code



// how many rows we have in database

$query   = "SELECT COUNT(*) AS numrows FROM mp3s  WHERE artist='$artist'Order by album";

$result  = mysql_query($query) or die('Error, query failed');

$row     = mysql_fetch_array($result, MYSQL_ASSOC);

$numrows = $row['numrows'];



// how many pages we have when using paging?

$maxPage = ceil($numrows/$rowsPerPage);



// print the link to access each page

$self = $_SERVER['PHP_SELF'];

$nav  = '';



for($page = 1; $page <= $maxPage; $page++)

{

   if ($page == $pageNum)

   {

      $nav .= " $page "; // no need to create a link to current page

   }

   else

   {

      $nav .= " <a href=\"$self?page=$page\">$page</a> ";

   }

}





// creating previous and next link

// plus the link to go straight to

// the first and last page



if ($pageNum > 1)

{

   $page  = $pageNum - 1;

   $prev  = " <a href=\"$self?page=$page\">[Prev]</a> ";



   $first = " <a href=\"$self?page=1\">[First Page]</a> ";

}

else

{

   $prev  = '&nbsp;'; // we're on page one, don't print previous link

   $first = '&nbsp;'; // nor the first page link

}



if ($pageNum < $maxPage)

{

   $page = $pageNum + 1;

   $next = " <a href=\"$self?page=$page\">[Next]</a> ";



   $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";

}

else

{

   $next = '&nbsp;'; // we're on the last page, don't print next link

   $last = '&nbsp;'; // nor the last page link

}

?>







<p>









<tr>

<td><?php echo $first; ?></td>

<td><?php echo $prev; ?></td>

<td><?php echo" Showing page $pageNum of $maxPage pages " . $next; ?></td>

<td><?php echo $last; ?></td>



</tr>

</body>

</html>

Submit page code:

<?php


echo "<html>";
echo "<head>";
echo "</head>";



?>

<form method="post" action="pages.php">
<input type="text" name="search" />
<input type="submit" />
</form>
</body>
</html

Not sure why it will work with text and not a variable.

Please Help!

mysql php

Edited 14 Years Ago by gazza7 because: n/a

2 Contributors
10 Replies
195 Views
9 Hours Discussion Span
Latest Post 14 Years Ago Latest Post by gazza7

nonshatter 26 Posting Whiz

14 Years Ago

Not exactly sure about this, but it could be because your search input field is saved as a variable. When you navigate to the next page, the variable is lost. You may be able to save the input text in a session, so it is available across all pages of the site!

$_SESSION['artist'] = $_POST['search'];
$artist = $_SESSION['artist'];
//query

You will also need a session_start(); at the top of your page if you go with this method.

Edited 14 Years Ago by nonshatter because: n/a

nonshatter 26 Posting Whiz

14 Years Ago

hmm, that's a strange one! Surely if the variable is being passed, then there should be no reason why it won't work with the query? Your code looks good from what I can see.

Perhaps try testing with something along the lines of this:

if (isset($artist))
{
    //do the query
}
else 
{
    die("Artist Variable is Empty!");
}

If you get 'artist variable is empty', at least you know it is a problem with the variable not being passed, and not with anything else.

Edited 14 Years Ago by nonshatter because: added code for testing variable

nonshatter 26 Posting Whiz

14 Years Ago

Yes, the variable is being passed, but only once for the first page. You will need to store the user-supplied data in a session, rather than a variable for it to work across multiple pages! If you have not used sessions before, have a look at a tutorial like this one: http://php.about.com/od/advancedphp/ss/php_sessions.htm

The code I suggested in my first post gives you the majority of what you need:

<?php
    session_start();                           //this needs to be the first line on each page you need the variable for
    $_SESSION['artist'] = $_POST['search'];    //store the users input into a session variable
    $artist = $_SESSION['artist'];             //extract the user input from the session and store it back into a variable for each page.

    //.....then issue the mysql query exactly as you have been doing
?>

Hope that helps. Give us a shout if you get stuck

Edited 14 Years Ago by nonshatter because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

gazza7 0 Newbie Poster · Answer 1 · 2010-10-19T23:39:48+00:00

Not exactly sure about this, but it could be because your search input field is saved as a variable. When you navigate to the next page, the variable is lost. You may be able to save the input text in a session, so it is available across all pages of the site!
$_SESSION['artist'] = $_POST['search'];
$artist = $_SESSION['artist'];
//query
You will also need a session_start(); at the top of your page if you go with this method.

Hi thanks for the reply, I did

echo $artist;

on the page to see if the variable was being passed, and it was. I also tried running the form from the same page, but with the same results.

gazza7 0 Newbie Poster · Answer 2 · 2010-10-20T00:26:29+00:00

The variable is being passed because the first page shows the first 15 records from the database as requested, its when you click on the next button, to show the next 15 records I get the table headings but no data.

I've just put them up on my web page for you to try. Enter U2 in the submit text box.

The page with the submit which uses the variable is: www.mp34u.tzo.com/submit.php
The page using the text is: www.mp34u.tzo.com/text.php

you will see what happends.

Thanks again

gazza7 0 Newbie Poster · Answer 3 · 2010-10-20T01:11:29+00:00

Yes, the variable is being passed, but only once for the first page. You will need to store the user-supplied data in a session, rather than a variable for it to work across multiple pages! If you have not used sessions before, have a look at a tutorial like this one: http://php.about.com/od/advancedphp/ss/php_sessions.htm
The code I suggested in my first post gives you the majority of what you need:
<?php
    session_start();                           //this needs to be the first line on each page you need the variable for
    $_SESSION['artist'] = $_POST['search'];    //store the users input into a session variable
    $artist = $_SESSION['artist'];             //extract the user input from the session and store it back into a variable for each page.

    //.....then issue the mysql query exactly as you have been doing
?>
Hope that helps. Give us a shout if you get stuck

I've added this code to the top of the pages.php page, but it still does not send the data to the next page.

<?php

   session_start(); 
 
    $_SESSION['artist'] = $_POST['search']; 
  
    $artist = $_SESSION['artist']; 



echo "<html>";

echo "<head>";

echo "</head>";



echo "<body>";





?>

Since each page is sent to the same page, why does it lose the variable?

nonshatter 26 Posting Whiz · Answer 4 · 2010-10-20T01:25:43+00:00

Well technically it's not the same page as you're passing a different parameter ($_GET) for each set of 15 results. The session should work. Test it using the code I posted

if (isset($artist))
{
    //do the query
}
else 
{
    die("Artist Variable is Empty!");
}

If you still can't get it working, I will try and replicate it on my server

gazza7 0 Newbie Poster · Answer 5 · 2010-10-20T01:53:49+00:00

Well technically it's not the same page as you're passing a different parameter ($_GET) for each set of 15 results. The session should work. Test it using the code I posted
if (isset($artist))
{
    //do the query
}
else 
{
    die("Artist Variable is Empty!");
}
If you still can't get it working, I will try and replicate it on my server

Hi

I get variable is empty when I go to the next page.

nonshatter 26 Posting Whiz · Answer 6 · 2010-10-20T02:00:34+00:00

Right, I have another idea. You could post the content of the variable across pages through the URL.
This should be the easiest solution. Add the content of the $artist variable to the URL so that it is sent across to the next/previous page. Forget about the session for now.

<?php

      $next = " <a href=\"$self?page=$page&artist=$artist\">[Next]</a> ";   //add artist var

?>

Then get the URL parameter using the $_GET method

<?php
if(isset($_POST['artist']))
{
    $artist = $_POST['artist']; //get artist from input field
}
else
{
    $artist = $_GET['artist'];  //otherwise, get the artist from the URL parameter
}
?>

gazza7 0 Newbie Poster · Answer 7 · 2010-10-20T02:18:10+00:00

Right, I have another idea. You could post the content of the variable across pages through the URL.
This should be the easiest solution. Add the content of the $artist variable to the URL so that it is sent across to the next/previous page. Forget about the session for now.
<?php

      $next = " <a href=\"$self?page=$page&artist=$artist\">[Next]</a> ";   //add artist var

?>
Then get the URL parameter using the $_GET method
<?php
if(isset($_POST['artist']))
{
    $artist = $_POST['artist']; //get artist from input field
}
else
{
    $artist = $_GET['artist'];  //otherwise, get the artist from the URL parameter
}
?>

Thanks for all your time, it works with sending the $artist with the url.

I think I will have to do the same with the urls prev, first and last page.

Much appreciated, cannot thank you enough.