Solved

How would I do this insert statement

Posted on 2014-10-23
11
73 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 108

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

708 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

15 Experts available now in Live!

Get 1:1 Help Now