Solved

PHP add and insert into database from a query

Posted on 2013-01-09
13
223 Views
Last Modified: 2013-06-10
Hi,
I have a php script that is supposed to insert data from a query, the query works in navicat, but I have obviously got this totally wrong, any ideas?

<?php
$username="********";
$password="********";
$database="**************";
$connprop="************";

mysql_connect($connprop,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");


mysql_query('CREATE TABLE order1

SELECT
`order`.telephone,
`order`.order_id,
`order`.store_name,
`order`.email,
`order`.payment_firstname,
`order`.payment_lastname,
`order`.payment_postcode,
`order`.total,
`order`.order_status_id,
`order`.date_added,
`order`.payment_method
FROM
`order`
WHERE
`order`.order_status_id > '0' AND `order`.date_added >= DATE_SUB(CURDATE(),INTERVAL 0 DAY)
ORDER BY
`order`.date_added ASC') or die(mysql_error());
?>
0
Comment
Question by:tonypearce
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 38758718
What error are you getting?

You need to assign the resource to something, like this:
$result = mysql_query('CREATE TABLE order1

SELECT 
`order`.telephone,
`order`.order_id,
`order`.store_name,
`order`.email,
`order`.payment_firstname,
`order`.payment_lastname,
`order`.payment_postcode,
`order`.total,
`order`.order_status_id,
`order`.date_added,
`order`.payment_method
FROM
`order`
WHERE
`order`.order_status_id > '0' AND `order`.date_added >= DATE_SUB(CURDATE(),INTERVAL 0 DAY)
ORDER BY
`order`.date_added ASC') ;

if(!$result){
die('error');
}

//Now loop though the resource:
while ($row = mysql_fetch_assoc($result)) {
    echo $row['email'];
    echo $row['lastname'];
    echo $row['postcode'];
    echo $row['age'];
}

Open in new window

0
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 38758730
Ah sorry, I misread the query, thought it was a select, not an insert.

OK, in that case, the user that is being called has the permissions to create tables right?
0
 

Author Comment

by:tonypearce
ID: 38758798
Yes, I use a drop if exists and that works fine
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 11

Expert Comment

by:Slimshaneey
ID: 38758859
OK, the command looks fine, but Im wondering if its failing because of the sort. Remove the order by clause from the select and see if that works?
0
 
LVL 10

Expert Comment

by:d4durvesh
ID: 38758881
on which PHP version you are running this
0
 

Author Comment

by:tonypearce
ID: 38758914
5.2
0
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 38759006
After you run the command, what value is in mysql_error?

Can you echo mysql_error ?
0
 

Author Comment

by:tonypearce
ID: 38759012
Hi,

No error. blank page, Not sure how to echo an error either!!
0
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 38759017
Also, can you change the code to this:

$username="********";
$password="********";
$database="**************";
$connprop="************";

$link = mysql_connect($connprop,$username,$password);
mysql_select_db($database) or die( "Unable to select database");


$result = mysql_query('CREATE TABLE order1

SELECT 
`order`.telephone,
`order`.order_id,
`order`.store_name,
`order`.email,
`order`.payment_firstname,
`order`.payment_lastname,
`order`.payment_postcode,
`order`.total,
`order`.order_status_id,
`order`.date_added,
`order`.payment_method
FROM
`order`
WHERE
`order`.order_status_id > '0' AND `order`.date_added >= DATE_SUB(CURDATE(),INTERVAL 0 DAY)', $link);
if(!$result){
echo mysql_error($link);
die();

Open in new window


One last thing, your status id, is it varchar or a number in the table definition? Why is the 0 wrapped in quotes?
0
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 38759036
You should also consider enable error reporting for all errors in the php.ini file while debugging this, alternatively, add this to the top of your page and run again:

ini_set('display_errors', 1);
error_reporting(E_ALL);

Open in new window

0
 
LVL 10

Expert Comment

by:d4durvesh
ID: 38759063
Try this,

<?php
$username="********";
$password="********";
$database="**************";
$connprop="************";

mysql_connect($connprop,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");


mysql_query('CREATE TABLE order1

SELECT
`order`.telephone,
`order`.order_id,
`order`.store_name,
`order`.email,
`order`.payment_firstname,
`order`.payment_lastname,
`order`.payment_postcode,
`order`.total,
`order`.order_status_id,
`order`.date_added,
`order`.payment_method
INTO order1
FROM
`order`
WHERE
`order`.order_status_id > '0' AND `order`.date_added >= DATE_SUB(CURDATE(),INTERVAL 0 DAY)
ORDER BY
`order`.date_added ASC') or die(mysql_error());
?> 

Open in new window

0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 38762503
Here is the strategy I would employ.

1. Create the SELECT query in its own PHP variable string.  Print the variable string so you can visualize exactly what the query contains. Run it.  Use a while() iterator to retrieve the rows and print them with var_dump().  This will let you see if there are any rows that are found by the SELECT portion of the query.  I am a little suspicious of this clause (it seems to look for an event in the future), and I want to be sure you actually have some rows in the results set:

AND `order`.date_added >= DATE_SUB(CURDATE(),INTERVAL 0 DAY)

2. After you're sure that the SELECT query runs, and you're sure that the SELECT query produces the right results set, return to the CREATE TABLE query.  Again, write it in its own string variable and print it out before passing it to mysql_query().  Then test the results of the query something like this:

$sql = "CREATE..."; // FILL THIS OUT WITH YOUR QUERY STRING
var_dump($sql);
$res = mysql_query($sql);
if (!$res) die(mysql_error());


You may find that the use of INTO is the problem.  I have never used that keyword in my CREATE...SELECT queries.  But whatever the issue might be, if you deconstruct the complicated stuff into a smaller set and build up from there, you will be able to visualize the errors along the way and you will find the point when the error is introduced.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39234875
According to the EE Grading Guidelines we are entitled to an explanation when the author of a question gives a marked down grade.  I'd like you to post that explanation here, along with an explanation of why you left this question hanging without comment since January.

Looking forward to hearing why you did this, ~Ray
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question