Solved

How would I do this insert statement

Posted on 2014-10-23
11
90 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
[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
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
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.

 
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 110

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 49

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This article discusses four methods for overlaying images in a container on a web page
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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.

691 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