SQL_CALC_FOUND_ROWS using ODBC driver
Hi, I've used PHP with MySQL and I was able to count the rows of a table by using the following code: SELECT SQL_CALC_FOUND_ROWS * FROM MyTableName; SELECT FOUND_ROWS(); However, I would like to do the same thing using Visual Basic 6... When I set the RS.Source to the first SELECT statement and open it, it run OK but I need to close the RS and open it again using the second SELECT statement. After any of this operations I get a Recordset-RecordsCount = 1 How can I work this out? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP
The second major problem I am running into after the upgrade is the
following error, which did not occur on Development.
Error Executing Database Query. Cannot convert value '-00-00
00:00:00'
from column 4 to TIMESTAMP.
The error occurred on line 8.
MySQL version is: 5.0.27
Thanks in advance for any help.
I don't know what/how are you doing and I don't even know enough about the
subject but:
From PHP manual @ http://pt.php.net/manual/en/function.strtotime.php:
Note: The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54
GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that correspond
to the minimum and maximum values for a 32-bit signed integer.) Additionally,
not all platforms support negative timestamps, therefore your date range
may be limited to no earlier than the Unix epoch. This means that e.g. dates
prior to Jan 1, 1970 will not work on Windows, some Linux distributions,
and a few other operating systems. PHP 5.1.0 and newer versions overcome
this limitation though.
From MySQL manual @
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html:
The range is '1970-01-01 00:00:01' UTC to partway through the year 2037.
TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01
00:00:00' UTC). A TIMESTAMP cannot represent the value '1970-01-01 00:00:00'
because that is equivalent to 0 seconds from the epoch and the value 0 is
reserved for representing '-00-00 00:00:00', the “zero” TIMESTAMP value.
HTH
- Nuno
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP
I'm simply executing a SELECT query that used to work before, same database, everything the same except a higher version of the MySQL server. In that case you are probably facing some uncompatible issue between the versions you used. Maybe someone else is able to help you more that I :( Sorry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT from 3 tables - Need help
Hi All, I'm working on a Intranet database with a few tables to allow all the workers from the company to access it. However, I'm facing a problem with a QUERY. I've did try to use JOIN but I'm not able to get the results I need... I have 3 tables (concerning this problem): Table 'Quotes' QuoteID ClientID Date Price Comments Table 'Clients' ClientID Name Address Email Table 'Products' QuoteID ProductName ProductType ProductShape After filling the info to table quotes, I would like to do a QUERY to SELECT a specific QuoteID and also to JOIN to the result the client information (being ClientID the relation) and also to JOIN the information of the product if available (being QuoteID the relation). The problem is that if there is no data in table 'Products' that matched QuoteID from table 'Quotes' than, the field 'QuoteID' is returned empty. I would like to do a SELECT that would return all the fields in the 'Quotes' table plus the client info and plus the product info but only if available. If product info is not available I would like to get a result having all 'Quotes' info and 'Clients' info. My query is: SELECT * FROM `Quotes` LEFT JOIN (`Products`,`Clientes`) ON (`Products`.`QuoteID`=`Quotes`.`QuoteID` AND `Clientes`.`ClientID`=`Quotes`.`ClientID`) WHERE `Quotes`.`QuoteID`=6936 Any suggestion? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT from 3 tables - Need help
Olexandr Melnyk wrote: I din't know about listiтg multiple tables in the JOIN clause up to now, but anyways it looks like QuoteId from the Products table is overriding the same field from the Quotes table. Try to replace the asterisk with an explicit list of fields you want to get. Hi, thanks for the reply (you should reply to the list also) Do you mean that I should replace SELECT * FROM with SELECT field1, [field2],[...] FROM? If so, I need to specify the table name like SELECT Clients.Name correct? Also, how can I access to Quotes.QuoteID and Products.QuoteID? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Row position in table when sorted?
Hello, I'm working on a web site which have a page for the admin to browse the clients table in a database. To facilitate the interaction, I'm ORDERing BY Name... The pagination buttons in this page are something like: href=.?Page=7 and when loaded, it will SELECT * FROM Clients ORDER BY Name LIMIT 7,1 This is working perfectly but now I have a problem. I've done a page to find a client and I display the clients names in a table (Using CSS) but I don't know where to link to??? I apply a query and retrieve let's say 14 clients that match. What should the LIMIT value??? This is what I don't know how to do... Please help? Is there a way to know the position of a certain row if all the rows are selected and in a specific order? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row position in table when sorted?
Rolando Edwards wrote: I think you need this syntax SELECT * FROM Clients ORDER BY Name LIMIT offset,rows Your offset must be rows per page * pagenumber where minimum pagenumber is 0 You should also count the full result set divide that count by the number of rows per page to get the last page number If the remainder is not zero, then add one to the max pagenumber Yes!! But that's what I have already done and it is working :) My problem is, when I go to the find client page and enter a client name, and for example receive 14 results that match, then I don't know what is the offset that I should use to link to the page that displays the client info for each one of those results... Let me try to explain with an example: Table Clients = 01 Ana Lucia 02 Mark David 03 George Carlin 04 Nelson Martin 05 Xavier Pitt 06 Martin Luther 07 Tony Andrew 08 Mark Donald 09 Petter James When I'm navigating the clients page, I'm seeing all this ID's, names, and a lot more information... In that page I have buttons First 335 336 337 338 339 340 341 Last I'm ordering by client name so my table looks like this Table Clients = 01 Ana Lucia 03 George Carlin 02 Mark David 08 Mark Donald 06 Martin Luther 04 Nelson Martin 09 Petter James 07 Tony Andrew 05 Xavier Pitt Each one of this buttons links to one of this pages, for example, if a user clicks on 337 it links to href=clients.php?recID=337 337 is not the ID of the client but the position of the client in the table when ordered by client name. When I search for a client Mar the WHERE will add a % and I will query with WHERE Name LIKE 'Mar%' This way I will retrieve the records Mark David Mark Donald Martin Luther My problem/question is how can I know the link for each one of these found records??? Which is the offset for the LIMIT??? In this case, by watching to the tables above I know that the limits would be 3, 4 and 5 but how can I know this with php/MySQL code? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row position in table when sorted?
Jerry Schwartz wrote: Now that I re-read your question, I think your answer is that the LIMIT clause applies to the set of records retrieved after all of the conditions are applied. In your example, SELECT * FROM clients WHERE name LIKE Mark% ORDER BY name LIMIT 1, 7; Then SELECT * FROM clients WHERE name LIKE Mark% ORDER BY name LIMIT 8, 7; Is that what you were asking? Have you looked at using the HANDLER statement? [...] Well... Yes and No! Yes, The examples that you provided with the LIMIT would work but... Yes, that could be be what I was asking but it doesn't fit on my needs. You guys don't have access to my code because if you have you would know that I'm just a beginner and my code wouldn't be compatible with your suggestions. But not everything is bad news... Now I know that the values that I set with LIMIT are not related with the position of my data in the table but to the position of the data after I apply a filter (WHERE)... I will reprogram the Show Client page to use WHERE RelationID=#NUM# instead of LIMIT #NUM#,1 So... Thank you guys and I will probably come back :) (Off-Topic: I'm using Thunderbird/1.5 Gmail and I do a Reply All which sends the messages to the person I'm replying and CC's to the list but I never receive my own messages. Is this the normal behavior? Thanks) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
