Solved

How would I do this insert statement

Posted on 2014-10-23
11
86 Views
Last Modified: 2014-10-24
Here's my code:

$nelson="insert into twitter_csv (actor_id, actor_display_name, posted_time, lat, lon, location_name, posted_day) SELECT actor_id, actor_display_name, posted_time, geo_coords_lat ,geo_coords_lon, location_name, posted_day FROM $the_table_name WHERE (geo_coords_lat BETWEEN '$lat_1' and '$lat_2') and (geo_coords_lon BETWEEN '$lon_1' and '$lon_2')";

Works great...but I've got to include a session_id as well as the everything else in my select statement. How would I do that?
0
Comment
Question by:brucegust
11 Comments
 

Author Comment

by:brucegust
ID: 40400222
Just in case my question is as clear as mud, here's what I've got to do:

$nelson="insert into twitter_csv (actor_id, actor_display_name, posted_time, lat, lon, location_name, posted_day, SESSION_ID) SELECT actor_id, actor_display_name, posted_time, geo_coords_lat ,geo_coords_lon, location_name, posted_day FROM $the_table_name WHERE (geo_coords_lat BETWEEN '$lat_1' and '$lat_2') and (geo_coords_lon BETWEEN '$lon_1' and '$lon_2')";

Where I've got SESSION_ID - I need to include that as part of the insert dynamic but now I've got a mismatch between the number of columns in the table being populated and the number of columns from which I'm grabbing from.

How do I code this correctly?
0
 
LVL 58

Accepted Solution

by:
Gary earned 300 total points
ID: 40400223
Wheres the session id going?

$nelson="insert into twitter_csv (actor_id, actor_display_name, posted_time, lat, lon, location_name, posted_day,session_id) SELECT actor_id, actor_display_name, posted_time, geo_coords_lat ,geo_coords_lon, location_name, posted_day,'session_id' FROM $the_table_name WHERE (geo_coords_lat BETWEEN '$lat_1' and '$lat_2') and (geo_coords_lon BETWEEN '$lon_1' and '$lon_2')";
0
 

Author Comment

by:brucegust
ID: 40400228
So I don't have to worry about the fact that the session id is not a part of the table from which I'm grabbing from?

That was my concern. I figured I had to have a complete match between the table I was inserting into and the table I was grabbing from .
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 58

Expert Comment

by:Gary
ID: 40400230
No, you can pass in your own string in apostrophes
0
 

Author Comment

by:brucegust
ID: 40400241
So, this is what I'm looking at, correct?

$the_table_name=$_GET['table_name'];
      $the_new_count=$_GET['new_count'];
      $lat_1=$_GET['lat_1'];
      $lat_2=$_GET['lat_2'];
      $lon_1=$_GET['lon_1'];
      $lon_2=$_GET['lon_2'];
      $session_id=$_GET['the_session_id'];
      
      $nelson="insert into twitter_csv (actor_id, actor_display_name, posted_time, lat, lon, posted_day, session_id) SELECT actor_id, actor_display_name, posted_time, geo_coords_lat ,geo_coords_lon, posted_day, '$session_id' FROM $the_table_name WHERE (geo_coords_lat BETWEEN '$lat_1' and '$lat_2') and (geo_coords_lon BETWEEN '$lon_1' and '$lon_2')";
0
 
LVL 58

Expert Comment

by:Gary
ID: 40400245
Yep
0
 

Author Comment

by:brucegust
ID: 40400319
Gary, here's what it looks like:

insert into twitter_csv (actor_id, actor_display_name, posted_time, lat, lon, location_name, posted_day, session_id) SELECT actor_id, actor_display_name, posted_time, geo_coords_lat ,geo_coords_lon, location_name, posted_day, '65vosjhaaurnmjkv0qha4gook4' FROM twtr_20140701 WHERE (geo_coords_lat BETWEEN '24.4' and '24.5') and (geo_coords_lon BETWEEN '-105.9' and '-100')

It's not working. No errors, but nothing being inserted into the database. What am I missing?
0
 
LVL 58

Expert Comment

by:Gary
ID: 40400331
The sql is fine, that there are no errors suggest there are no matching rows.
Does it work without the session column?
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 40400511
No errors, but nothing being inserted into the database. What am I missing?
How do you know the query worked?  Have you tried the SELECT query separately?

Also, please learn about the "escape" functions.  They will save your database one day.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 40400994
If the select query produces no rows, no inserts will happen.

As Ray suggests, please try the select query independently
      SELECT
            actor_id
          , actor_display_name
          , posted_time
          , geo_coords_lat
          , geo_coords_lon
          , location_name
          , posted_day
          , '65vosjhaaurnmjkv0qha4gook4'
      FROM twtr_20140701
      WHERE (geo_coords_lat BETWEEN '24.4' AND '24.5')
            AND (geo_coords_lon BETWEEN '-105.9' AND '-100')

Open in new window

Try this in a query window (phpMyAdmin?)
Does that query produce any rows?
0
 

Author Comment

by:brucegust
ID: 40402171
Morning folks!

As it turned out, y'all were correct in suspecting that my query wasn't "wrong" as much is it was a situation where there wasn't any date that matched my search criteria. Since I was sailing in uncharted waters with my syntax, I was inclined to think I had dropped the ball with my code, but once I tried it in my phpMyAdmin interface, I was able to prove its accuracy as well as establish some legitimate criteria so I could better tweak things.

Thanks everyone for weighing in!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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 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…

820 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