Solved

PHP add and insert into database from a query

Posted on 2013-01-09
13
220 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 108

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 108

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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…
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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…

895 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now