Pass a variable to SQL

dcjones

Hi all, again. I have spent the afternoon reading posts for the solution to my next problem.

I AM LEARNING FAST.

I am passing a variable from a form script 1 to script 2.

I know the variable is there, I can echo $test, and I can see the correct data.

I want now to use the content of the variable to replace the name of a feild in a table. This is what I have so far.

From the form I have

if (isset($_GET['select_by'])) {
 $colname_search_results = (get_magic_quotes_gpc()) ? $_GET['select_by'] : addslashes($_GET['select_by']);
}
$test = ($_GET['select_by']);
echo $test;

mysql_select_db($database_Chandos, $Chandos);
$query_select_by = "SELECT $test  FROM chandos_books";
$select_by = mysql_query($query_select_by, $Chandos) or die(mysql_error());
$row_select_by = mysql_fetch_assoc($select_by);
$totalRows_select_by = mysql_num_rows($select_by);

The variable is $test

The table feild name is author_name

I have tried and tried with this, what I need is a good SQL/PHP manual. But I am learning ever day.

Kind regards, keep safe and well.

Dereck

rachel2004

From the description of your problem, it sounds like you want to issue an UPDATE statement since you plan on modifying values in your table. See the MySQL manual for details, but here it is very simply:

UPDATE myTable
SET column1 = 'newValue1',
column2 = 'newValue2'
WHERE id = 'current_id_value';

dcjones

Hi Rachel,

I wish it was that simple.

What I am trying to do is this. Pass a variable from my form the a script on anthor page.

the variable = ....?select_by=title

title could be a number of different things.

In the second script I want to use the content of the variable in place of a feild name in my table. I know I can't just use the variable as a feild name (feild names are fixed).

The outshot of the problem is this.

User selects from a list/menu on form in script 1.

The form action load script 2.

passing "http://www.mydomain.com/catalogue/booklist_by.php?select_by=title

I have my SQL staement in script 2 as :

if (isset($_GET['select_by'])) { 
 $colname_search_results = (get_magic_quotes_gpc()) ? $_GET['select_by'] : addslashes($_GET['select_by']); 
} 
$test = ($_GET['select_by']); 
echo $test; 

mysql_select_db($database_Chandos, $Chandos); 
$query_select_by = "SELECT $test  FROM chandos_books"; 
$select_by = mysql_query($query_select_by, $Chandos) or die(mysql_error()); 
$row_select_by = mysql_fetch_assoc($select_by); 
$totalRows_select_by = mysql_num_rows($select_by);

The "title" part of the URL which was passed to the script 2 is the variable I want to use in place (wrong wording) of the feild name in the table.

So that the results from the table show only but all the records that have data the title feild.

Or, if the variable was "author" just to show all the authors .

Any advise, apart from go for a beer.

Kind regards

Dereck

rachel2004

I'm looking at the PHP code you supplied and it looks fine to me. In other words, assuming $_GET['select_by'] actually contains a value, everything looks like it would work. Where is the problem that you are having?

One small change I would make, though:

if (isset($_GET['select_by'])) 
{
    $colname_search_results = (get_magic_quotes_gpc()) ? $_GET['select_by'] : addslashes($_GET['select_by']); 
    $test = $_GET['select_by']; 
} 
echo $test;

or like this:

if (isset($_GET['select_by'])) 
{
    $test = (get_magic_quotes_gpc()) ? $_GET['select_by'] : addslashes($_GET['select_by']); 

} 
echo $test;

dcjones

Hi rachel.

$test does display the content.

its the part where I am using $test the my query:

$query_select_by = "SELECT $test  FROM chandos_books";

The query does not return any results if I use:

SELECT $test FROM chandos_books;

If I echo $test it returns "author_name", "author_name" is one of the feilds in the table "chandos_books".

Having slept on it for a night I now see that I must echo the content of $test into my query, but how is the question.

Any idea.

Kind regards, keep safe and well

Dereck

rachel2004

Dereck, it shouldn't be necessary for you to echo the value of $test into your query. The way you have it posted:

$query_select_by = "SELECT $test FROM chandos_books";

is legal and assuming that $test has a value, it should do the query. If you want to see what your query actually looks like, do this:

$query_select_by = "SELECT $test FROM chandos_books";
echo $query_select_by;

As far as not returning any results, that of course depends on whether there are values that match your query. What happens if you hard code the value of $test into the query:

$query_select_by = "SELECT column_name FROM chandos_books";

Lastly, here is one more thing you can try:

$query_select_by = "SELECT ".$test."  FROM chandos_books";

dcjones

Hi Rachel,

Thanks for your reply. Still having problems. If it would help you can see the script in action at:

http://www.chandospublishing.com/catalogue/booklistTEST.php

and use the dropdown list on the left at the bottom, marked TEST MENU.

This is waht I have so far:

if (isset($_GET['select_by'])) {
  $colname_select_by = (get_magic_quotes_gpc()) ? $_GET['select_by'] : addslashes($_GET['select_by']);
}
$test = ($_GET['select_by']);

echo $test; //THIS IS JUST ME TESTING THE CONTENT OF THE VARIABLE

echo $select_by; //THIS IS JUST ME TESTING THE CONTENT OF THE VARIABLE, AND YES THE VARIABLES DO CONTAIN THE CORRECT DATA.

$maxRows_select_by = 10;
$pageNum_select_by = 0;
if (isset($_GET['pageNum_select_by'])) {
  $pageNum_select_by = $_GET['pageNum_select_by'];
}
$startRow_select_by = $pageNum_select_by * $maxRows_select_by;

mysql_select_db($database_Chandos, $Chandos);
$query_select_by =  "SELECT ".$test." FROM chandos_books";
$query_limit_select_by = sprintf("%s LIMIT %d, %d", $query_select_by, $startRow_select_by, $maxRows_select_by);
$select_by = mysql_query($query_limit_select_by, $Chandos) or die(mysql_error());
$row_select_by = mysql_fetch_assoc($select_by);

if (isset($_GET['totalRows_select_by'])) {
  $totalRows_select_by = $_GET['totalRows_select_by'];
} else {
  $all_select_by = mysql_query($query_select_by);
  $totalRows_select_by = mysql_num_rows($all_select_by);
}
$totalPages_select_by = ceil($totalRows_select_by/$maxRows_select_by)-1;

And later on in the script I have:

do {

 echo $row_select_by ['.$test.'];

</a></div></td>
</tr>
<tr>
<td class="formcolheadersmall"> </td>
<td class="bodytext"> </td>
<td width="102" class="bodytext"> </td>
</tr>

 } while ($row_select_by = mysql_fetch_assoc($select_by));

Can you let me know waht you think.

Kind regards, keep safe and well.

Dereck

rachel2004

Dereck, I will take a visit probably later today. Need to get out of the house. Meanwhile, though, here is a snippet of your code:

if (isset($_GET['select_by'])) {
  $colname_select_by = (get_magic_quotes_gpc()) ? $_GET['select_by'] : addslashes($_GET['select_by']);
}
$test = ($_GET['select_by']);

Assuming that $GET['select_by'] is not set, what are you expecting $test = ($GET['select_by']); to contain?

Do an if/else to assing a default value to $test if $GET['select_by'] is not set:

if (isset($_GET['select_by'])) {
  $colname_select_by = (get_magic_quotes_gpc()) ? $_GET['select_by'] : addslashes($_GET['select_by']);
  $test = ($_GET['select_by']); 
} else {
    $test = "bar";
}

Otherwise, the possibity that you could be feeding the variable $test to your query, not realizing that it's possible for $test not to contain a value.

I will write again later when I have time to review your work.

tsinka

Hi dcjones,

I noticed that you use the same variable name twice:

echo $select_by;

$select_by = mysql_query($query_limit_select_by, $Chandos) or die(mysql_error());

You wrote "and later on". Do you use $select_by to store the GET variabe select_by between those two parts ? This would corrupt the result id of the query.

Try to avoid using the same variable name for different purposes. Use something like $res = mysql_query(...) or something like that.

Since the query itself looks ok and the part at the beginning, too ... it must have something to do with corrupting the result id.

Thomas

tsinka

One other point:

Try to use

echo ${"row_select_by['".$test."']"};

instead of

echo $row_select_by ['.$test.'];

or change the query to:

"SELECT ".$test." AS field FROM chandos_books";

and then

echo $row_select_by['field'];

edit:

I just looked at the source code of the page and it seems that your code walks successfully through the result set. There are all the rows. Only the output of the chosen field is missing. So I suggest that it has something to do with the code mentioned above in this post.

Thomas

dcjones

Hi Rachel,

There are at the moment three possible strings that could be in the $test variable.

$test is populated by the passing of the URL from the form on the first script.

Possible content:

isbn_paperback
book_title
author_name.

By selecting one of the above from the list/menu the form passes the variable to the second script.

You will see this happening when you visit the site. I am currently echoing the contents of the variable and the query just for testing.

I hope this helps.

dcjones

Hi Rachel and tsinka

I think tsinka may have the solution.

I will get back to you both.

Thanks tsinka

rachel2004

In addition to posts made by Thomas, I found another area where you need to use an "else" clause to improve it. Here is what you have:

if (isset($_GET['pageNum_select_by'])) {
  $pageNum_select_by = $_GET['pageNum_select_by'];
}
$startRow_select_by = $pageNum_select_by * $maxRows_select_by;

With the code you have above, you are assuming that the check for $GET['pageNum_select_by'] will always be TRUE since you start using the variable $pageNum_select_by outside of your "if" block. Of course, the question then is: if you are so confident that the line will be true, why bother using an "if" check to start with. Why not just do:

$pageNum_select_by = $_GET['pageNum_select_by'];
$startRow_select_by = $pageNum_select_by * $maxRows_select_by;

However, as I put in my previous post, it's better to have some sort of default value in case the "if" check turns out to be false. Do something like this:

if (isset($_GET['pageNum_select_by'])) {
  $pageNum_select_by = $_GET['pageNum_select_by'];
}
else
{
    $pageNum_select_by = "default_value";
}
$startRow_select_by = $pageNum_select_by * $maxRows_select_by;