Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP How-To
|
PHP How-To Post your "How do I do this with PHP?" questions here.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP How-To section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 6th, 2007, 02:20 AM
Registered User
 
Join Date: Oct 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Temporary Tables Challenge!

Hello all you PHP/MySQL experts out there;

I am trying to write a script in PHP which will create a temporary table, insert some data into it from an existing table, and then run a select statement on the temporary table data.

I can do this without a problem on my MySQL server when I use the following in the SQL window against the database and not a table;

CREATE TEMPORARY TABLE l_table SELECT home_team AS Team, home_score AS Pts
FROM tblResults WHERE date >= '2007-09-13' AND division = '2';
INSERT INTO l_table SELECT away_team AS Team, away_score AS Pts FROM tblResults WHERE date >='2007-09-13' AND division = '2';
SELECT Team, Count(Pts)AS Pld, Sum(Pts) AS Pts FROM l_table
GROUP BY TEAM ORDER BY Pts DESC

But when I put this into PHP like this;

$query = "CREATE TEMPORARY TABLE l_table (Team varchar(50), pld integer (2), Pts integer (3))TYPE=HEAP";
    "LOCK TABLE l_table WRITE";
            "SELECT home_team AS Team, home_score AS Pts FROM tblResults WHERE date >= '2007-09-13' AND division = '" . $_GET['division_id'] ."'";
    "INSERT INTO l_table SELECT away_team AS Team, away_score AS Pts FROM tblResults WHERE date >='2007-09-13' AND division = '2'";
    "SELECT Team, Count(Pts)AS Pld, Sum(Pts) AS Pts FROM l_table GROUP BY Team ORDER BY Pts DESC";
    "DROP TABLE l_table";
    "UNLOCK TABLE l_table";

$result = mysql_query($query, $conn)
  or die(mysql_error());
$num = mysql_num_rows($result);


But but get php error messages;
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in #### on line 26

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in #### on line 46

Where am I going wrong???

Is it that the table is not being created and populated?
Do I need to include the USE statement against the database?


Any advice would be much, much, much, appreciated!






 
Old November 8th, 2007, 09:42 AM
Registered User
 
Join Date: Nov 2007
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to asanga
Default

You have to give permission to the user to create temporary table, drop temporary table. Have you given all permission to the user?

Asanga Amarawansa
asanga@needmatch.biz
http://www.needmatch.biz





Similar Threads
Thread Thread Starter Forum Replies Last Post
I Challenge U! Sync of Two Sets of Related Tables Odeh Naber Access VBA 1 April 24th, 2007 06:53 AM
creating temporary tables instead of queries clwk Access VBA 4 March 12th, 2007 02:50 PM
Crystal Report 8.0 - Working with Temporary Tables deebeedee VB How-To 0 July 27th, 2006 07:30 AM
Temporary tables eadred ADO.NET 4 August 8th, 2005 12:49 AM
Sql Server Temporary Tables itHighway SQL Server 2000 1 July 14th, 2005 12:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.