stevieontario

thanks Mchl, Unfortunately I got this message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM perform CROSS JOIN (SELECT MAX(id) AS sourceid FROM Source) AS sq U' at line 2

Morning PHPFreaks, this might be an easy problem to solve but I'm stumped. I have two tables: Source (id, filename, filedatetime) Perform (performid, sourceid, machinename, machinetype, output) Every hour one new row will go into Source (source.id is an auto-incremented primary key), and 100 new rows will go into Perform. The 100 new rows that go into Perform will each have the same sourceid (which is of course the same number as source.id). My query: I want to select perform.output for each hour, and group the results by machinetype, and order the results by output. Then I can look at output trends for specified time periods. My problem: I want to query perform for output in the most recent hour, i.e., to retrieve the most recent id. So far the only way I have been able to do that is like so: SELECT machinetype AS "Machine type", format( sum( output ) , 0 ) AS "Machine output", FROM perform WHERE sourceid =790 GROUP BY machinetype ORDER BY sum( output ) DESC This of course requires that I know that the last sourceid is indeed 790. My question: how can I achieve the same result without having to learn the specific sourceid? Thanks in advance!

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.

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?

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.

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: 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).

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.

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.

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

Good morning PHP Freaks: I'm curious about the difference between the two functions Mysql_insert_id and last_insert_id. Other than that the first is a PHP function and the second belongs to MySql, they both appear to do the same thing, which is to get the most recently generated auto-incremented value. If that is the case, which is the better one to use? The reason I ask: I have a Table1 which updates hourly (i.e., adds a new row every hour) and a Table2 which contains hourly production data on roughly 100 machines. Table1's id is an auto-incremented field (and the primary key). I want Table2 to hold a column with Table1's id, so that every hour Table2 will get 100 or so new rows with the same Table1 id. This way, I can query production data over time and date ranges. Any ideas/suggestions?

Correction on my previous reply: I said "Anyway, I tried making $db_connection global, but got the original warning." Not exactly right. The warning actually reads "9 is not a valid MySQL-Link resource in [filepath/filename]"

Thanks Maq and PFMaBiSmAd, Should have mentioned that I tried leaving the argument blank -- i.e. calling mysql_insert_id() -- but received another warning, which was: "... A link to the server could not be established in [filepath/filename]" For some reason I thought the first warning was more important. Anyway, I tried making $db_connection global, but got the original warning. If you have any suggestions, I appreciate your time!

Hello PHP Freaks: I'm playing in a xampp sandbox and trying to insert the last auto-incremented id of one table into another table. I am using the function mysql_insert_id(), in the following way: $lastSid = mysql_insert_id($db_connection); ... and here is the database connection code from my config file: 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; } I keep getting the error "supplied argument is not a valid MySQL-Link resource." I have checked the db connection, and everything seems fine. (The other queries I have run work.) Any ideas what I've done wrong? Mysql version 5.1.30 PHP version 5.2.8

well as it turns out the only php.ini file I was able to change was the right one. phpinfo() now indicates curl is enabled, by god! Thanks for your time, everyone -- much appreciated!

badbad, Actually, the message says I cannot "create" the file. I found more than one "php.ini" when I did a search, and was able to make your recommended change and save it. But there are two others whose search results do not indicate a path beginning with "C://". I cannot save those two others; I get the message: Cannot create the C:// etc temporary directory. Weirdness! I tried attaching a screenshot showing the error message, but it got rejected.

thanks badbad and everyone else, I'm not a Windows expert, and I suspect the following subsequent problem has to do with Windows. I "found" the file you refer to (xampp/apache/bin/php.ini) but not by navigating to the folder. When I navigated to the folder I couldn't see a file called "php.ini" or any text file called "php." Rather, I "found" php.ini it using the Windows search feature, which indicated that php.ini is in the referenced folder. I also found the relevant line and made the recommended change. But couldn't save it (I guess Search puts everything into a temp folder). So I just navigated to xampp/apache/bin and saved it from Notepad. This created a new file (i.e., didn't overwrite the existing one). So the code still won't execute.

Thanks maq. Here's the relevant code: function curl_string($url,$user_agent,$proxy=0) { $ch = curl_init(); //curl_setopt ($ch, CURLOPT_PROXY, $proxy); curl_setopt ($ch, CURLOPT_URL, $url); curl_setopt ($ch, CURLOPT_USERAGENT, $user_agent); curl_setopt ($ch, CURLOPT_HEADER, 0); curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1); curl_setopt ($ch, CURLOPT_FOLLOWLOCATION, 1); curl_setopt ($ch, CURLOPT_TIMEOUT, 120); $result = curl_exec ($ch); curl_close($ch); return $result; }

Morning php Freaks: I'm playing in a sandbox (xampp) and testing some code (php 5.2. containing the function curl_init. A very rudimentary version of my web app is running, successfully, using curl_init() at one point. But when I test my code in my test environment using the identical function, I get the following error: Fatal error: Call to undefined function curl_init() in C:\Program Files\xampp\htdocs\dev\Functions1.php on line 100 Anybody have any ideas what's going on? Thanks!

Promiso, You may be right, and I won't argue. Regardless of which database, I still have to figure out the loop problem. i.e., I still have to pick through the results of the initial loop (described above) and insert each machine's hourly numbers into a table. Any suggestions?

Thanks Mark, I have considered this but can't see an alternative. I have 100 machines of various makes and models, many running continuously almost all the time. I want to be able to report each machine's output/capability, to be able to report total output by machine type (make, model, etc.) as well as by other system metrics. I have one table that has the make, model, etc. of each machine (i.e., 100 records). I have another table that records hourly performance (this contains the date/time key you suggest). But I can't see how to be able to report machine-level info unless I (1) create one table for each machine, or (2) create one table for each hour. The first alternative seems the best; it's big but at least it's finite.

Hi everyone, I have successfully tested the following code: <?php foreach ($machine as $k=>$v){ if ($k == "MachineName"){ $unit = $v; } if ($k == "Outputs"){ foreach($v as $outputs) { foreach($outputs as $k=>$v) { if ($k == "Widgets"){ $totalwidget = $v; }elseif(is_array($v)){ foreach ($v as $kk=>$vv){ if ($kk == "Widgets") $totalwidgets = $vv; } } } } } if ($k == "Capabilities"){ foreach($v as $capabilities){ foreach($capabilities as $k=>$v){ if ($k == "Widgets"){ $potentialwidgets = $v; }elseif(is_array($v)){ foreach ($v as $kk=>$vv){ if ($kk == "Widgets") $potentialwidgets = $vv; } } } } } } if ($totalwidgets != 0) $capabilityfactor = $totalwidgets/$potentialwidgets; else $capabilityfactor = 0; It now outputs 100 or so lines, representing the total number of machines in the plant. Each line contains four items, as follows: Machine1, [a number representing Machine1's $totalwidgets in that hour], [a number representing Machine1's $potentialwidgets in that hour], [a number representing Machine1's $capabilitfactor in that hour] i.e., the first item in each line is a particular machine; the next three items are that machine's performance numbers in that hour -- output, capability, capability factor. I now want to put each line into its own separate database table. i.e., I'll have 100 or so separate tables, each representing one machine. That way I can track each machine's performance hour after hour. I figure I'll need to build 100 or so individual queries to insert the contents of each line into the corresponding table. So my question: what loop statement(s) should I use to accomplish this? Thanks in advance!

Hello php experts: I have gone through a 124-element array (converted from an xml file using simplexml_file_load) and I can now output 124 lines of information, with 4 items on each line. I now want to put the contents of each line into a separate database table, i.e., I will have 124 database tables. Racking my brain to figure it out, but I'm stumped. Any ideas? I'm using PHP Version 5.2.8

Thanks everyone, Maybe View will solve my problem, thanks Keith. Thanks also to Ken and Fenway. Fenway, I take your point, and I'll post info on version, table structure etc. asap.

Hello everyone, I have two tables (Table A and Table B) and want to define Field B2 in Table B so that it multiplies the contents of Field B1 in Table B with the content of Field A2 in Table A. Is this possible?