[sqlite] Baffling SQLite statement
Hey all, For the life of me I can't figure out why the following statement returns no rows: SELECT productcodesize,options FROM bsp_options WHERE modelID=351 AND '1990' = startyear AND '1990' = endyear I'm looking right now at a printout of records from the query: SELECT productcodesize,options FROM bsp_options WHERE modelID=351 Where the fields are: modelID: 351 startyear: 1990 endyear:1993 ..and there are a bunch of them. Can anyone spot anything wrong with that query??? Thanks! -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
Jay A. Kreibich wrote: Do you really mean for the years to be string literals and not numbers? I've tried it both ways to no avail. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
Hey, I'm not sure what you mean by what is below: BareFeet wrote: Try this: mode insert select * from bsp_options where modelID=351; Is that two separate SQL statements? I'm testing all this using the SQLiteAdmin tool, so do I paste what you have above in the SQL window? I tried that and got an error near mode back. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
Hey D. Richard all, Casting them as int's also did not work. Here's the schema: CREATE TABLE bsp_options ( optionsID INTEGER NOT NULL PRIMARY KEY, modelID INT(11) NOT NULL, startyear VARCHAR(4) NOT NULL DEFAULT '0', endyear VARCHAR(4) NOT NULL DEFAULT '0', options TEXT NOT NULL, productcodesize VARCHAR(10), productdesc VARCHAR(200), pattern VARCHAR(10) ); D. Richard Hipp wrote: On May 19, 2008, at 8:42 PM, Skip Evans wrote: Hey all, For the life of me I can't figure out why the following statement returns no rows: SELECT productcodesize,options FROM bsp_options WHERE modelID=351 AND '1990' = startyear AND '1990' = endyear My guess is that you are storing endyear as an integer within a column with no affinity. And an integer is always less than a string so '1990'=endyear is always false. What is your table schema? Have you tried: WHERE modelID=351 AND CAST(startyear AS INT)=1990 AND CAST(endyear AS INT)=1999 I'm looking right now at a printout of records from the query: SELECT productcodesize,options FROM bsp_options WHERE modelID=351 Where the fields are: modelID: 351 startyear: 1990 endyear:1993 ..and there are a bunch of them. Can anyone spot anything wrong with that query??? Thanks! -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
D. Richard Hipp wrote: What does this show: SELECT DISTINCT typeof(startyear) FROM bsp_options; SELECT DISTINCT typeof(endyear) FROM bsp_options; I pasted those two statements into the SQL window and it returned: 2 query has been executed. 1.04 msec. 0 Line has been modified. But no data. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
What happens if the field in the table is defined as int(11) but the insert command wraps the values with single quotes? In MySQL this makes no difference. All the operations still work the same. Is SQLite different? Thanks! Skip -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
Even this statement SELECT * FROM bsp_options WHERE modelID=351 and startyear=1990 Is returning no rows and I can clearly see dozens that meet the criteria! Here is the table def. I've recreated and repopulated them using int(11) for the year fields: CREATE TABLE bsp_options ( optionsID INTEGER NOT NULL PRIMARY KEY, modelID INT(11) NOT NULL, startyear INT(11) NOT NULL, endyear INT(11) NOT NULL, options TEXT NOT NULL, productcodesize VARCHAR(10), productdesc VARCHAR(200), pattern VARCHAR(10) ); AAAaaarrrgghhh!!! I'm eating my head from the inside out Skip-- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
I don't have access to a command line tool. This server is at a client's hosting facility and I have no shell acess. BareFeet wrote: Sorry, I left out a dot. It should be: .mode insert select * from bsp_options where modelID=351; You enter tat using the command line tool. It will reveal the insert statements used to create the data, which will show the years as either 1990 (numeric) or '1990' (text). Tom BareFeet http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
Hey all, Here's the table definition: CREATE TABLE bsp_options ( optionsID INTEGER NOT NULL PRIMARY KEY, modelID INT(11) NOT NULL, startyear INT(11) NOT NULL, endyear INT(11) NOT NULL, options TEXT NOT NULL, productcodesize VARCHAR(10), productdesc VARCHAR(200), pattern VARCHAR(10) ); The insert statement that creates the records looks like this: $sql=INSERT INTO bsp_options (modelID,startyear,endyear,options, productcodesize,productdesc) VALUES ($modelID, .$arr[3]., .$arr[4]., '.sqlite_escape_string($arr[5]).', '.$arr[7].', '.$arr[6].'); After this statement is used in a loop that inserts 14,000 records I can see in SQLiteAdmin there are dozens of records that fit the criteria. SELECT * FROM bsp_options WHERE startyear=1990 Produces no results. SELECT * FROM bsp_options WHERE startyear='1990' Produces no results. SELECT * FROM bsp_options WHERE startyear like '1990%' Produces results!!! Is it treating the data like a string? Why is the like qualifier working??? -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
I'm looking at the data in SQLiteAdmin, where I can just browse and see records that meet the criteria. I can search on modelID=351 to display the records, but then when I add and startyear=1990 it returns no records, even though I can see with the first query records where this condition is in fact met. P Kishor wrote: On 5/19/08, Skip Evans [EMAIL PROTECTED] wrote: Even this statement SELECT * FROM bsp_options WHERE modelID=351 and startyear=1990 Is returning no rows and I can clearly see dozens that meet the criteria! If you are not getting rows returned, how can you clearly see dozens of rows that meet your criteria? What you are seeing, from what I can tell from your earlier emails, is what is being shown in whatever program you are using to look at SQLite data. It could be that this program that you are using is showing you something other than what is really in the db. Since, in another email you mention that you don't have access to the command line tool because you are accessing the database on someone else's server, is it possible for you to download that database on to your own computer where you can access it from the command line shell? -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
D. Richard Hipp wrote: On May 19, 2008, at 9:16 PM, Skip Evans wrote: D. Richard Hipp wrote: What does this show: SELECT DISTINCT typeof(startyear) FROM bsp_options; SELECT DISTINCT typeof(endyear) FROM bsp_options; I pasted those two statements into the SQL window and it returned: 2 query has been executed. 1.04 msec. 0 Line has been modified. That text didn't come from SQLite. I'm using sqliteadmin. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
Thanks Puneet!!! SELECT '_' || startyear || '_' FROM bsp_options WHERE startyear LIKE '1990%' You results may look like so _1990 _ Notice the empty space after 1990? This was it! The client's csv file had an extra space at the end of the year. I didn't know this because I have no shell access to the server, so I can't get directly at the file. I can only write code based on the layout he gives me, column names and numbers. I called his sys admin and he says, Well, I'm having dinner right now. B**s*ard! If I have to work as long as it takes to get this done, he can send me the @$%#$ csv file! And sure enough, there were trailing spaces in all the numeric fields! I just did a trim() when doing the INSERT. Thanks again all for all the help. I'm just sorry it turned out to be so simple. Also, I think being new to SQLite I'm probably focusing on it, thinking it must be working differently than other databases I have more experience with, and losing site of the raw data elements I'm working with. Live and learn, and a BIG thanks again to all. Now I can watch The Amazing Mrs. Pritchard and go to bed. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Equivalent of mysql_real_escape_string() ?
Hey all, Okay, I'm looking all through the PDO docs on php.net, but am unable to find the SQLite equivalent to the MySQL function mysql_real_escape_string() in case, among other things, a text field contains single quotes, etc. How is this done in SQLite? I'm still scouring the the docs but having no luck. Does it have something to do with $dbh-prepare() ...or am I on the wrong track with that one? -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What is the PDO last_insert_id method???`
Hey all, I'm new to SQLite and having a heck of a time finding the equivalent to MySQL's mysql_insert_id(). I'm using SQLite with PDO in PHP5. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FOUND IT!!! What is the PDO last_insert_id method???`
Hey all, Found the PHP site page with all the PDO functions... gadzoooks! Skip Skip Evans wrote: Hey all, I'm new to SQLite and having a heck of a time finding the equivalent to MySQL's mysql_insert_id(). I'm using SQLite with PDO in PHP5. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] numRows undefined function?
Hey all,
I'm new to the list and SQLite, although I have
about 7 years with MySQL, and going back to Oracle
and Informix a rock's age with databases.
I have the following code in a PHP 5.2.5 install.
$sql=SELECT * FROM bsp_model WHERE makeID=$makeID
ORDER BY model;
$result = $dbHandle-query($sql);
// if 0 records returned, reload drop down with
empty, single selection.
$norows=$result-numRows();
if ($norows==0)
{
echo 'false';
exit;
}
But when it runs I get the following error:
Call to undefined method PDOStatement::numRows()
All the documentation I see on SQLite shows this
as a valid method.
I know that $result is a valid DB object
containing data because if I simply comment out
the test for zero records everything works fine
(because there is data in the object), but I still
need the test for zero records.
Any suggestions would be greatly appreciated.
Skip
--
Skip Evans
Big Sky Penguin, LLC
503 S Baldwin St, #1
Madison, WI 53703
608-250-2720
http://bigskypenguin.com
=-=-=-=-=-=-=-=-=-=
Check out PHPenguin, a lightweight and versatile
PHP/MySQL, AJAX DHTML development framework.
http://phpenguin.bigskypenguin.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] numRows undefined function?
Hey Ty all, Yes, rowCount() worked, and thanks for the reference to the podstatement.php page. But why did so much documentation I found on the web use numRows()? What is the difference between the PDOStatement set of functions and the set to which numRows() belongs to? Different versions of SQLite? Thanks again, Skip Ty wrote: Skip Evans [EMAIL PROTECTED] writes: But when it runs I get the following error: Call to undefined method PDOStatement::numRows() All the documentation I see on SQLite shows this as a valid method. If I'm reading correctly, $result is of type PDOStatement. From the documentation, it doesn't look like numRows is a function on that object... http://us.php.net/manual/en/class.pdostatement.php I would try $result-rowCount() and see if that gets you what you need. (The docs for that function claim it doesn't work for all types of databases... I don't know how it works with sqlite). ~Ty ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
