Solved

PHP add and insert into database from a query

Posted on 2013-01-09
13
227 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 110

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 110

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

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

632 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