returning value from php function, can't get it to work

[stevieontario]

Morning PHP Freaks,

 

I'm having the damnedest time pulling an id value out of one db and inserting it into another. I don't think there's anything wrong with the query -- I tested it in phpMyAdmin and it works. But I just can't get the value, which is the most recently generated id, into my php program. (Working with Xampp, php v. 5.2.8, mySQL Server version: 5.1.33-community).

 

I want to take the most recent id out of INFO and then insert it into PERFORMANCE.

 

Here's the code:

function getsourceid()
{
$gquery = "select id from INFO order by id desc limit 1";

sqlquery($gquery) == $value;
return $value;

}

$lastid = 	getsourceid($value);

 

 

If anybody can point out where I'm going wrong, much appreciated!

 

Thanks

[kickstart]

Hi

 

Basics:-

 

function getsourceid()
{
$gquery = "select id from INFO order by id desc limit 1";
$value = sqlquery($gquery);
return($value);
}

$lastid = 	getsourceid();

 

This assumes sqlquery is a function that takes a sql query and returns a value.

 

All the best

 

Keith

[stevieontario]

Keith, many thanks! I got a weird error when I tried sqlquery($gquery) = $value;, and assumed that the programming version of the commutative law would apply in these cases, i.e., that $value = sqlquery($gquery); is the same as sqlquery($gquery) = $value;.

 

Then, for some reason I got it into my head that "==" would solve the problem.

 

 

[Andy-H]

== is used for statements to compare the operators used in the statement. When assigning a value to a variable the variable being assigned the value must be on the left hand side of the '=' assignment operator. If you look into lvalue and rvalue you will be able to find more on this.

[stevieontario]

Keith, Andy: thanks to both of you.

 

Keith, your assumption that sqlquery is a function that takes an sql query and returns a value is correct. Code is here:

	function sqlquery($myquery,$sets=0)
{
	global $db_hostname,$db_name,$db_username,$db_password;

	$db_connection = mysql_connect($db_hostname, $db_username, $db_password) or die("Could not connect: " . mysql_error());
	mysql_select_db($db_name, $db_connection);

	$results = mysql_query($myquery, $db_connection) or die(mysql_error());

	switch ($sets) {
		case 1:
			$sqlres = mysql_num_rows($results);
			break;
		case 2:
			$sqlres = mysql_fetch_array($results);
			break;
		default:
			$sqlres = $results;
	}

	mysql_close($db_connection);

	return $sqlres;

}

 

Perhaps not-so-strangely, this leads to a further problem. $lastid is supposed to go into the table PERFORMANCE (via another insert query), but only zeros show up.

[akitchin]

Keith, Andy: thanks to both of you.

 

Keith, your assumption that sqlquery is a function that takes an sql query and returns a value is correct. Code is here:

	function sqlquery($myquery,$sets=0)
{
	global $db_hostname,$db_name,$db_username,$db_password;

	$db_connection = mysql_connect($db_hostname, $db_username, $db_password) or die("Could not connect: " . mysql_error());
	mysql_select_db($db_name, $db_connection);

	$results = mysql_query($myquery, $db_connection) or die(mysql_error());

	switch ($sets) {
		case 1:
			$sqlres = mysql_num_rows($results);
			break;
		case 2:
			$sqlres = mysql_fetch_array($results);
			break;
		default:
			$sqlres = $results;
	}

	mysql_close($db_connection);

	return $sqlres;

}

 

Perhaps not-so-strangely, this leads to a further problem. $lastid is supposed to go into the table PERFORMANCE (via another insert query), but only zeros show up.

 

i see no mention of $lastid in any of your code (nor do i see mention of the PERFORMANCE table). you may want to clarify the problem..

[stevieontario]

akitchin: thanks for your time. Yes, some explanation is in order.

 

Two tables: INFO and PERFORMANCE. The program grabs an xml file from an external site, posts some administrative information (time, date, xml filename) into INFO, then loops through the xml output for certain information, then inserts the resultss into PERFORMANCE.

 

This happens every hour (i.e., the external site publishes a new xml file every hour).

 

The xml file contains performance data on 100+ machines. So each xml file contains hourly data on the machines' output.

 

Each row in PERFORMANCE contains data on each machine's output at a particular hour/date.

 

Therefore, each hour, my program will insert one row into INFO, and 100+ rows into PERFORMANCE. I want each one of the 100+ rows going into PERFORMANCE to contain the same id (which is an auto-increment field in INFO).

 

That's what $lastid is for (BTW -- $lastid is explained in the first two posts in this thread).

 

 

 

[akitchin]

ah, my mistake - sorry, should have read the first post more closely. you're not sending a second parameter to sqlquery(), which will default to $sets = 0, and therefore will just return the resource. this means getsourceid() will also only return the resource, not the actual id. therefore to get the last id using this current setup, you'd need to go:

 

$resource = getsourceid(); // use the function kickstart provided
$lastid = $resource['id'];

 

however it's worth noting that mysql_insert_id() will automatically fetch the last ID that was inserted (when you INSERTed into INFO), so you can easily do this:

 

// define and run the INSERT query for INFO
$lastid = mysql_insert_id();
// use $lastid for the PERFORMANCE INSERTs

 

saves you some server resources and a pointless function.

[stevieontario]

Thanks again. Trouble is, mysql_insert_id() also results in just a bunch of zeros. That's actually why I decided to write the custom function getsourceid() in the first place.

 

Same with the mysql function last_insert_id() -- when I run the query from phpMyAdmin, I get a column whose row total equals the number of rows in INFO.

 

 

 

 

[akitchin]

is the ID in info an auto_increment INT primary key?

[stevieontario]

yes, it's an auto-increment primary key. I should also have mentioned that I got the following error:

 

Warning: mysql_insert_id() [function.mysql-insert-id]: A link to the server could not be established in C:\Program Files\xampp\htdocs\etc.

[akitchin]

yes, it's an auto-increment primary key. I should also have mentioned that I got the following error:

 

Warning: mysql_insert_id() [function.mysql-insert-id]: A link to the server could not be established in C:\Program Files\xampp\htdocs\etc.

 

ah, then that's your issue. i suspect you're running the INSERT using sqlquery(), which closes the database connection after the query. when mysql_insert_id() is called, it tries to use the last opened connection (if is still open), or it tries to establish a new connection as if mysql_connect() were called without any parameters. it won't find any currently open ones, so it tries to establish a connection and, as the error says, fails to do so.

 

since the getsourceid() is a workaround in the first place, it might be worth re-thinking your strategy regarding queries. perhaps add a parameter to sqlquery() that allows you to stipulate that the connection be left open after the query is run?

[stevieontario]

Okay, maybe that's it. Again, thanks.

 

The code for sqlquery() is here:

function sqlquery($myquery,$sets=0)
   {
      global $db_hostname,$db_name,$db_username,$db_password;

      $db_connection = mysql_connect($db_hostname, $db_username, $db_password) or die("Could not connect: " . mysql_error());
      mysql_select_db($db_name, $db_connection);

      $results = mysql_query($myquery, $db_connection) or die(mysql_error());

      switch ($sets) {
         case 1:
            $sqlres = mysql_num_rows($results);
            break;
         case 2:
            $sqlres = mysql_fetch_array($results);
            break;
         default:
            $sqlres = $results;
      }

      mysql_close($db_connection);

      return $sqlres;

   }

 

Is the second argument ($sets=0) the culprit?

[akitchin]

it is, but only partially, for your first issue (which was getsourceid() not working). see above for how to make it work.

 

i would suggest adding a third parameter to the function that allows you to keep the connection alive after closing. since you usually won't care about getting the results from an INSERT query anyway, you could even just add a case to the $sets switch() that does this:

 

function sqlquery($myquery,$sets=0)
   {
      global $db_hostname,$db_name,$db_username,$db_password;

      $db_connection = mysql_connect($db_hostname, $db_username, $db_password) or die("Could not connect: " . mysql_error());
      mysql_select_db($db_name, $db_connection);

      $results = mysql_query($myquery, $db_connection) or die(mysql_error());

      switch ($sets) {
         case 1:
            $sqlres = mysql_num_rows($results);
            break;
         case 2:
            $sqlres = mysql_fetch_array($results);
            break;
         case 3:
            $sqlres = $db_connection;
            break;
         default:
            $sqlres = $results;
            break;
      }

      if ($sets != 3)
         mysql_close($db_connection);

      return $sqlres;

   }

 

whenever you send a statement to sqlquery() that you don't want closing the connection afterward, send it with 3 as the second parameter:

 

$query = 'INSERT INTO info stuff';
$conn = sqlquery($query);
$lastid = mysql_insert_id($conn);

 

this is a bit of band-aid solution, as it's been spliced into your function instead of redesigning the function itself, but it should work. did you write sqlquery() yourself?

[stevieontario]

thanks akitchin, appreciate your time. No I didn't write the function myself, just learning by doing (and asking!). I have been picking my way through.

 

I'll try what you suggested and see what happens.