Jump to content
Larry Ullman's Book Forums

Select ... Into In Mysql


Recommended Posts

Larry, I have 2 of your books: "PHP 6 and MySQL 5 for Dynamic Web Sites" and "Effortless E-Commerce with PHP and MySQL", but do not have your MySQL book. If I just need to get this book to answer my question, that's fine, but I wasn't sure that my answer would be found there. I have made a lot of progress, but can't figure this out:

 

I am needing to get a value from a column in xacres table and multiply it by a value in xinputs table. But I can't figure out how to save the first value as a variable to be used by the SELECT statement for the second table. I have the following:

 

$q1 = 'SELECT * FROM xacres WHERE user_id=' . $_SESSION['user_id'] . '&& filler=' . '"Acres owned"' . 'LIMIT 1';

$r1 = mysqli_query($dbc, $q1);

if (mysqli_num_rows($r1) >= 1 ) {

$q2 = 'SELECT * FROM xinputs WHERE user_id=' . $_SESSION['user_id'] . '&& user_id=' . $_SESSION['user_id'];

$r2 = mysqli_query($dbc, $q2);

if (mysqli_num_rows($r2) >= 1 ) { .....

 

As a test, the above works successfully, but I haven't yet saved the variable from $q1 to be used in $q2. To accomplish this, I have changed the code to the following (where "xacres01" is a column from xacres table and where "owned" is the name of the variable I am trying to save to):

 

$q1 = 'SELECT xacres01 INTO owned FROM xacres WHERE user_id=' . $_SESSION['user_id'] . '&& filler=' . '"Acres owned"' . 'LIMIT 1';

$r1 = mysqli_query($dbc, $q1);

if (mysqli_num_rows($r1) >= 1 ) {

$q2 = 'SELECT * FROM xinputs WHERE user_id=' . $_SESSION['user_id'] . '&& user_id=' . $_SESSION['user_id'];

$r2 = mysqli_query($dbc, $q2);

if (mysqli_num_rows($r2) >= 1 ) { .....

 

All I did was replace "SELECT *" with "SELECT xacres01 INTO owned". And I now get the error: "mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given" for the 1st mysqli_num_rows line, which I think means I have an error in the preceding 2 lines.

 

I don't know if my syntax is incorrect, or if I just need to do this in another way. Can you give me a suggestion? Or is the answer in the MySQL book? Thanks for your help!

Link to comment
Share on other sites

I'm currently in work and on my phone. But I shall look at this in more depth when I'm in. It seems to me that:

1 - rather than use * just select the column you need from each table.

2 - use mysql to return the array and list() the value from the column in xacres table. Basically you assign value from the DB straight a variable. 3 - do any validation you need to do I.e. You got a valid result from the query and run the second query, using the $var you got from using the list() function on the first query results.

 

Although I am still a bit confused as to what your trying to achieve, is this what you want to happen?

 

1 - Select 1 `column` from `xacres` table save the result as a variable

2 - Then use this variable inside the next MySQL query to get obtain the desired result.

 

Also do you expect the query to return 1 result if so then you don't need a LIMIT.

 

If it's going to return more than 1 result then how do you know which result you actually need.

  • Upvote 1
Link to comment
Share on other sites

Right, looking at the second query that is erroring.

 

The code should start something more like this:

 

 $q1 = 'SELECT xacres01 FROM xacres WHERE user_id=..............

 

I don't know your table or globals so I cant comment if they are right.

 

Then, run the query

 

$r1 = mysqli_query($dbc, $q1);

if (mysqli_num_rows($r) == 1) { // IF The result returned 1 array that is if you mean it to return just 1 array

list($owned) = mysqli_fetch_array($r, MYSQLI_NUM)

} else {
    echo 'error with num rows';
}

 

This will return a variable called $owned that should have the `xacres01` column in it. From there use the $owned variable inside the next MySQL query ($q2). something like

"SELECT `column` FROM `table` where owned='$owned'";

 

I haven't run this code from an editor, so just make sure i haven't omitted any ';' or other syntax.

Jonathon

  • Upvote 1
Link to comment
Share on other sites

If you know the error that's being thrown from MySQL rather than just from PHP then you might be able to get to the bottom of it a little easier - just use:

 

echo mysqli_error($dbc);

 

If you just want to return the product of the two values in separate tables you could just do it in one query like:

 

$q = "SELECT (table1.col2 * table2.col2) AS valueyouwant FROM table1, table2 WHERE table1.col1 = '3' AND table2.col1 = '4'";

 

Just for reference if you want to assign it to an intermediate variable then I'd keep it inside MySQL. You can setup a MySQL variable that can be used across subsequent queries within the same connection and then use SELECT... INTO something like this:

 

$q = "SELECT col1 INTO var FROM table1 LIMIT 1";

 

Also just to add a wider point - SELECT... INTO is not supported by MySQL for tables only for variables - if you wanted to achieve this the MySQL method would be either INSERT INTO... SELECT or CREATE TABLE... SELECT.

  • Upvote 1
Link to comment
Share on other sites

Thank you both so much! I'm digesting the suggestions. Summarizing my objective: There should be only 1 row resulting from $r1. I am then going to use that result to multiply it by a column in several rows resulting from $r2, using this product to update a second column in each of the $r2 rows.

 

It looks like I have 3 ways to do this: 1) using "list" outside of mysql (I didn't know about this possibility.) , 2) using "select ... into" within mysql, or 3) using a select statement that includes both tables at once. As I try to figure out the best option, I have 1 other question for now:

 

Which of these 3 would be most efficient?

 

Thanks!

Link to comment
Share on other sites

In general, where possible you should try and let the database do as much work as possible and this will be more efficient the majority of the time. That said now you've explained exactly what you plan on doing it discounts the single statement option. Overall in terms of performance I doubt there's any significant difference (depending upon the quantity of updates you require) the only real consideration is what is best practice. I'll briefly give my opinions on the three techniques - only my opinion other people may think differently.

 

1) Extracting the value back into your PHP script and then using that in a subsequent query is probably the simplest in terms of logic - so if you're still relatively new to PHP and MySQL it might be the best option for you. However in terms of lines of code this option would entail writing the most and having two round trips to the database server.

 

2) The single query won't quite work anymore now you've explained exactly what you want to do. Ideally you'd combine the first query I wrote into an update statement but unfortunately you can't reference the table that you're updating inside the select statement of a sub-query.

 

3) This option would allow you to let the database do all of the work which in my opinion is generally best practice. It would involve less code than option 1 but an extra database query to set up the initial variable - however these wouldn't be round trips as no processing would be required inside PHP. I've never seen an example but I assume you could send all three statements at once using mysqli_multi_query. (Have since checked and you can)

 

The procedure for 3 would now (in quasi-code) be:

 

  • Set up the variable in MySQL
  • Calculate the value using the first statement I provided and assign this using SELECT... INTO
  • Run the update query using the MySQL variable as the update value

 

Hope that all makes sense

  • Upvote 1
Link to comment
Share on other sites

 Share

×
×
  • Create New...