Solved

PHP add and insert into database from a query

Posted on 2013-01-09
13
222 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to send multiple emails at the same time in PHP 12 60
Select Query - Group By Function Producing Unexpected Results 8 19
Presenting past 6 months bug 7 29
PHP AJAX JSON 2 61
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
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…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

773 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