Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PHP add and insert into database from a query

Posted on 2013-01-09
13
Medium Priority
?
228 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 111

Accepted Solution

by:
Ray Paseur earned 1500 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 111

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

721 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