Re: [PHP-DB] Why the sudden dis-interest?
What's with all the people suddenly wanting to unsubscribe? Did they suddenly become experts - no longer needing the community for support? Or did they suddenly discover they had actually enlisted for the influx of emails they were getting and wanted to stop them? Sure seems odd I'll bet they lost interest at some point, but never bothered figuring out how to unsubscribe, and then saw one man (OP of that thread) have the nerve to ask publicly, please unsubscribe me, and they thought, ah, perfect opportunity to jump on that bandwagon, and so save save from having to be the lone odd public case, AND not have to figure how to do it myself. Just a guess. -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] convert special characters.
No one? Not even a link I can go look this up myself with? I have successfuly converted before, but this time the company is in the netherlands and their special characters are doing this. Also, I have the database set to UTF-8, but when I check the charset: $charset = mysql_client_encoding($con); it returns latin1? Can someone tell me what I am doing wrong here? Not looking for someone to do it for me. Just some help figuring out which way to look. Why is htmlentities() converting wrong? How are you determining that you are using UTF-8 charset? I think you want to set everything to UTF-8.. the database, the table (or even finer in case any setting has e.g. a column set to another charset), the script files, the HTML output, the browser, *everything*. Just because e.g. one sets the charset in the browser does not mean that Apache (or whatever webserver you are using) is set to serve that charset. -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] convert special characters.
notice there are also these if you end up needing them: iconv_set_encoding() iconv() http://us.php.net/manual/en/function.iconv-set-encoding.php http://us.php.net/manual/en/function.iconv.php -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Hmm. So would this be the quick and easy way to set everything to UTF-8? well... those functions will not to set everything to UTF-8. They merely do what they say they will do... e.g. for iconv, Performs a character set conversion on the string str from in_charset to out_charset. --- That (iconv's function) does not have anything to do with the charset *of the db* (or table, or what Apache is set to serve, or the file's charset, etc.). I always feel that the best thing is to learn and understand, so you know, ...exactly where your strings are becoming what/where/why... and figure out how to control all those places of possible transformation and where you might not have control, appreciate that is the situation, and figure out how to workaround it. I am not so expert either. I *assume* that if one did have control of all the places where encoding config-settings can factor in, then one would never need such a thing as iconv(). But PHP is so full of tools.. to give you power just when/where you need it. My coder spirit always wants to understand exactly what is going on. So if I were you I would want to figure out exactly where your strings are getting transformed into something other than what you expected.. and correct the issue at that spot. -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] convert special characters.
They are getting converted in those functions I posted. They display correctly when in the text field, but when inserted to the DB by that functions, they get converted. Then when I echo them out of the DB they are the converted chars. I think it may be the table or table cell, but I read somewhere on php.net that hemlentities doesn't always convert correctly. Hence my asking of this question. notice that htmlentities() has several params... incl. one for the charset: http://us.php.net/manual/en/function.htmlentities.php But now that I re-read your OP, I am thinking, WHY are you saving the data entity-ized at all? My understanding has always been to save the data just *as it is*.. and if you LATER need to entity-ize it (for example: for display on a webpage), then do that at the time you pull the data out of the db for display. Of course this would require your db/table/connection are all of a charset that can handle the characters you are asking it to save into the db. I have a UTF-8 db that saves content in dozens of languages... and it works fine.. and none of the data has been messed with at all from what the language-specific user input. -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Session and Access Privilages.
Hey, I am working on the application, thought its not OOP currently, I plan to take it further in a year or so. Right now, I have 4 access levels which define what the users can do on the application. Based on the access levels defined, a session varialble is set called $_SESSION['authtype'], and this defines what are the links shown to the user. so basically the session authtype, defines what links are shown to the users, now i am thinking that if a user comes to know of a link whihc he does not have access to, he / she can put that in the url and try to gain access, what i feel is there should be some check on each of the page for the access and if the acess criteria is not met, then the user should be sent back to him home page view. Any ideas on what the best way is to implement something like this? Any help is appreciated. Thanks, Vinay Hi Vinay If you are already checking your $_SESSION['authtype'] var, to know whether or not to display certain links.. then you can just add the same kind of checking at the top of any code that should be reserved for a certain level of user. If someone without proper access has hacked the URL then just redirect to your login page, or whatever you want to do to the intruders. HTH -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple Access to Database - The Answer
[snip] PS: I respectfully request that if a user asks a question, that an explicit answer be given, not just a reference to the manual. It could be then followed by a manual reference. The user then has his answer and can look up more detail in the manual. Thanks. Ethan Ethan, Glad you figured it out. What you wrote after that is silly. If someone on this (or any) list posts the reference to the right place in the right manual - which will answer your question, then consider yourself lucky, and well served. No one owes you a darn thing on these lists. And anyway if the referenced place in the manual already spells it out.. then why should someone re-type all that (or copy and paste all that)??! -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] COUNT() returns 0 if there were no matching rows. .... really?!
Hi all
I am 99.9% sure it is my lack of understanding something, but it sure
seems like my PHP/MySQL code is returning something other than what i
expect from reading the docs.
to explain:
this code is serving me (almost perfectly) well:
---
$query = SELECT COUNT(*) AS `CountRowsThisDateThisTBL`,
date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS
`tableAlias` FROM t7solar_landing GROUP BY date(solarLandingDateTime)
UNION ALL SELECT count(*) AS `CountRowsThisDateThisTBL`,
date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm'
AS `tableAlias` FROM aweber_7solar_confirm GROUP BY
date(solarAweberConfDateTime) UNION ALL SELECT count(*) AS
`CountRowsThisDateThisTBL`, date(solarAWDateTime) AS `uniqueDate`,
'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY
date(solarAWDateTime) ORDER BY uniqueDate DESC LIMIT 300;
$foundUniqueDateROWS = mysql_query($query) or die(query failed:
.mysql_error());
while ($uniqueDateROW = mysql_fetch_object($foundUniqueDateROWS)) {
$CountRowsThisDateThisTBL=0;
$uniqueDate=htmlentities($uniqueDateROW-uniqueDate);
$tableAlias=htmlentities($uniqueDateROW-tableAlias);
$CountRowsThisDateThisTBL=htmlentities($uniqueDateROW-
CountRowsThisDateThisTBL);
$TBLsubarray[$uniqueDateROW-tableAlias]=$CountRowsThisDateThisTBL;
$BuildPerUniqueDateArray[$uniqueDateROW-uniqueDate]=$TBLsubarray;
}
---
...but I am having the problem, that on iterations of the while loop
where there are NO records in one of those tables on a particular date
(when the *other* tables *do* have records for that date), then the
variable $CountRowsThisDateThisTBL seems to persist its previous value
(from what it was on the last iteration for the same table (previous
date, same table), as opposed to what I would expect (that it should
be set to 0 since count() should be returning 0 for this iteration.
What am I missing?
Here's an example (snip) from a var_dump of that
$BuildPerUniqueDateArray:
(note that the 'aweber_7solar_aw' table does NOT have a record for the
date '2009-07-28', so I would expect to see that 1 to be a 0 there.)
[2009-07-29]=
array(3) {
[aweber_7solar_aw]=
string(1) 1
[t7solar_landing]=
string(1) 1
[aweber_7solar_confirm]=
string(1) 1
}
[2009-07-28]=
array(3) {
[aweber_7solar_aw]=
string(1) 1
[t7solar_landing]=
string(1) 5
[aweber_7solar_confirm]=
string(1) 2
thanks,
-Govinda
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: COUNT() returns 0 if there were no matching rows. .... really?!
Here's an example (snip) from a var_dump of that
$BuildPerUniqueDateArray:
(note that the 'aweber_7solar_aw' table does NOT have a record for
the
date '2009-07-28', so I would expect to see that 1 to be a 0
there.)
If a table doesn't have a record for a given date, I wouldn't expect
to see 1
or 0 -- I would expect not to see any row at all for that date/table
combination. You're not looping through all possible dates, you're
looping
through the result-set of your query.
Are you sure that the table in question doesn't have any 2009-07-28
records?
You could add the following column to each SELECT to help
troubleshoot:
GROUP_CONCAT(date_column) AS `all_timestamps_for_date`
This will give your result-set an additional column, which will
contain a
comma-separated list of all the records that GROUP BY is gathering
together in
each row (and therefore all the records that COUNT() is counting).
I'm wondering if some sort of timezone discrepancy is maybe causing
a timestamp
record to be attributed to 2009-07-28 unexpectedly.
Ben
Ben
I tried to implement your troubleshooting column like so:
$query = SELECT GROUP_CONCAT(date(solarLandingDateTime)) AS
`all_timestamps_for_date` COUNT(*) AS `CountRowsThisDateThisTBL`,
date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS
`tableAlias` FROM t7solar_landing GROUP BY date(solarLandingDateTime)
UNION ALL SELECT GROUP_CONCAT(date(solarAweberConfDateTime)) AS
`all_timestamps_for_date` COUNT(*) AS `CountRowsThisDateThisTBL`,
date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm'
AS `tableAlias` FROM aweber_7solar_confirm GROUP BY
date(solarAweberConfDateTime) UNION ALL SELECT
GROUP_CONCAT(date(solarAWDateTime)) AS `all_timestamps_for_date`
COUNT(*) AS `CountRowsThisDateThisTBL`, date(solarAWDateTime) AS
`uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw
GROUP BY date(solarAWDateTime) ORDER BY uniqueDate DESC LIMIT 300;
it returns this error:
query failed: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near 'COUNT(*) AS `CountRowsThisDateThisTBL`,
date(solarLandingDateTime) AS `uniqueDat' at line 1
..So not to sit here helpless, I troubleshoot according to my current
level of skill this way:
I added this lower down on my page:
$tableDump = SELECT solarAWDateTime FROM aweber_7solar_aw ORDER BY
solarAWDateTime DESC;
$tableDumpResult = mysql_query($tableDump) or die(query failed:
.mysql_error());
echo hr /pre\n;
while ($row = mysql_fetch_assoc($tableDumpResult)) {
print_r($row);
}
echo /pre\n;
echo hr /\n;
it returns this:
Array
(
[solarAWDateTime] = 2009-08-06 13:33:57
)
Array
(
[solarAWDateTime] = 2009-08-06 09:41:54
)
Array
(
[solarAWDateTime] = 2009-08-06 06:06:55
)
Array
(
[solarAWDateTime] = 2009-08-05 16:19:27
)
Array
(
[solarAWDateTime] = 2009-08-05 16:19:25
)
Array
(
[solarAWDateTime] = 2009-08-05 16:19:02
)
Array
(
[solarAWDateTime] = 2009-08-05 06:55:58
)
Array
(
[solarAWDateTime] = 2009-08-04 06:46:42
)
...
Array
(
[solarAWDateTime] = 2009-07-30 06:48:56
)
Array
(
[solarAWDateTime] = 2009-07-29 16:11:20
)
I did not clip my paste here ^^^ the last entry is indeed '2009-07-29
16:11:20'.
..so you can see there is no record in this table with a timestamp on
the date '2009-07-28'.. so HOW in the world does my array get that
element, for that date, as if the while loop was iterating on a record
in this table with that date, when none exists?? (You still have my
OP on this?.. to see the code and var_dump for that array I build from
the iterating results of the original query?)
John Butler (Govinda)
govinda.webdnat...@gmail.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Just one other tiny point of style here: having given the expression date(solarAWDateTime) the alias uniqueDate, you should probably use that alias to refer to the same thing elsewhere in your query, such as in the GROUP BY column. So: ... That's how I'd write it, anyway. I like to know your thinking; I'm working to learn to 'think in MySQL'.. so every input is appreciated. thanks! -G -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Me/this again. this works good: $query = SELECT COUNT(*) AS `CountUniqueDatesInMyTbl`, date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS `tableAlias` FROM t7solar_landing GROUP BY uniqueDate ORDER BY uniqueDate DESC LIMIT 62 UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm' AS `tableAlias` FROM aweber_7solar_confirm GROUP BY uniqueDate ORDER BY uniqueDate DESC LIMIT 62 UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY uniqueDate ORDER BY uniqueDate DESC LIMIT 62; except that I just added the ORDER BY clause onto each SELECT segment, and now I get this error: query failed: Incorrect usage of UNION and ORDER BY How can I order the results while still doing the UNION ALLs? -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
I should have given an example .. select count(*) as record_count, date(column_name) as date_field, 'my_table' as table_name union all select count(*) as record_count, date(column_name) as date_field, 'my_table_2' as table_name and end up with: count | date | table_name --- 5 | 2009-01-01 | table 1 10| 2009-01-01 | table 2 Ben, Chris, This is as far as I could get so far: $query = SELECT COUNT(*) AS landing_count, date(solarLandingDateTime) AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing UNION ALL SELECT count(*) AS confirm_count, date(solarAweberConfDateTime) AS solarAweberConfDate, 'aweber_7solar_confirmALIAS' AS aweber_7solar_confirm UNION ALL SELECT count(*) AS thankyou_count, date(solarAWDateTime) AS solarAWDate, 'aweber_7solar_awALIAS' AS aweber_7solar_aw; $foundUniqueDateROWS = mysql_query($query) or die(query failed: .mysql_error()); giving this error: query failed: Unknown column 'solarLandingDateTime' in 'field list' but I most certainly do have a column named 'solarLandingDateTime' in the table named 't7solar_landing'. So I am not sure what it's unhappy. ? John Butler (Govinda) govinda.webdnat...@gmail.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Niel, thanks (I was wondering about that... thought it seemed odd
without a FROM clause)
Now I have this:
$query = SELECT COUNT(*) AS landing_count, date(solarLandingDateTime)
AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing FROM
t7solar_landing GROUP BY DATE(solarLandingDateTime) UNION ALL SELECT
count(*) AS confirm_count, date(solarAweberConfDateTime) AS
solarAweberConfDate, 'aweber_7solar_confirmALIAS' AS
aweber_7solar_confirm FROM aweber_7solar_confirm GROUP BY
DATE(solarAweberConfDateTime) UNION ALL SELECT count(*) AS
thankyou_count, date(solarAWDateTime) AS solarAWDate,
'aweber_7solar_awALIAS' AS aweber_7solar_aw FROM aweber_7solar_aw
GROUP BY DATE(solarAWDateTime) ;
$foundUniqueDateROWS = mysql_query($query) or die(query failed:
.mysql_error());
while ($uniqueDateROW = mysql_fetch_assoc($foundUniqueDateROWS)) {
echo tr
td.htmlentities($uniqueDateROW-solarLandingDate)/td
td.htmlentities($uniqueDateROW-landing_count)./td
td.htmlentities($uniqueDateROW-thankyou_count)./td
td.(some math coming)./td
td.htmlentities($uniqueDateROW-confirm_count)./td
td.(some math coming)./td
td.(some math coming)./td
/tr;
}
Which seems to be outputting (I assume) the correct number of records
(one for each unique date in each of the 3 tables, combined).
But those table cells are coming out empty; i.e.
$uniqueDateROW-solarLandingDate
and
$uniqueDateROW-landing_count
are returning nothing.
Can someone point me to understand why? I thought that:
SELECT COUNT(*) AS landing_count, date(solarLandingDateTime) AS
solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing FROM
t7solar_landing GROUP BY DATE(solarLandingDateTime)
would, among other thing, assign the number of records counted in that
first table to the alias 'landing_count'. No?
I know it is annoying to hand-hold when newbies are this clueless.
It's just hard for me to even know where to study when there are many
blanks in my understanding. I am going to systematically plod through
my books once I get them here. Meanwhile thanks to all who make time
to help!
-Govinda
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Yes, but when you UNION that query with others it gets a bit more
complicated
...
If you prefer the latter syntax, you can use mysql_fetch_object().
Ben
Bless you Ben!!
That last post of yours led to my first decent dose of real
significant SQL-syntax understanding since I started writing more than
the *simplest* of SQL statements which I had gleaned from an intro PHP
book. Your time will not go to waste! Thank you!
I got it performing as expected. But there is one remaining thing
that is mysterious to me. It will be easiest to explain/ask by
showing my code and the result:
this:
-
$query = SELECT COUNT(*) AS `CountUniqueDatesInMyTbl`,
date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS
`tableAlias` FROM t7solar_landing GROUP BY DATE(solarLandingDateTime)
UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`,
date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm'
AS `tableAlias` FROM aweber_7solar_confirm GROUP BY
DATE(solarAweberConfDateTime) UNION ALL SELECT count(*) AS
`CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`,
'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY
DATE(solarAWDateTime) ;
while { ...
echo htmlentities($uniqueDateROW['uniqueDate']).
.htmlentities($uniqueDateROW['CountUniqueDatesInMyTbl']).
.htmlentities($uniqueDateROW['tableAlias']).br /\n;
-
returns this:
2009-07-28 5 t7solar_landing
2009-07-29 1 t7solar_landing
2009-08-02 2 t7solar_landing
2009-08-03 3 t7solar_landing
2009-08-04 2 t7solar_landing
2009-07-28 2 aweber_7solar_confirm
2009-07-29 1 aweber_7solar_confirm
2009-07-30 1 aweber_7solar_confirm
2009-07-31 1 aweber_7solar_confirm
2009-08-01 2 aweber_7solar_confirm
2009-08-02 1 aweber_7solar_confirm
2009-08-03 2 aweber_7solar_confirm
2009-08-04 1 aweber_7solar_confirm
2009-07-29 1 aweber_7solar_aw
2009-07-30 1 aweber_7solar_aw
2009-07-31 1 aweber_7solar_aw
2009-08-01 2 aweber_7solar_aw
2009-08-02 1 aweber_7solar_aw
2009-08-03 2 aweber_7solar_aw
2009-08-04 1 aweber_7solar_aw
Now the first (date) and last (table) column are as expected. But
what happened to count(*)??
There ARE 5 unique dates represented in the first table, as the
highest returned value for count(*) shows, but as you can see, there
are 8 unique dates in the second table, and 7 unique dates in the last
table. Why then do we see only 1s and 2s in those columns for
the latter 2 tables? And why does NOT the first table's rows include
exclusively ONLY the values 1, 2, 3, 4, 5? I see two 2s
and no 4. (??)
I'm sure I can hack PHP in that while loop to generate the right count
of unique dates for each table.. but I want to take advantage of this
thread up to now and learn more SQL. The docs say count() Return[s]
a count of the number of rows returned. In relation to that
definition, I don't know what I am looking at in the results I got
above.
Can you 'splain me?
John Butler (Govinda)
govinda.webdnat...@gmail.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Taking this: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) Your query says: get the date() from the solarAWDateTime field then group by that date and give me the date and number of results ie - you asked for the number of results per date - which is what you got. You didn't ask for the number of unique dates. yes. And in fact that was what I wanted. I better quit for the day while I am ahead, and not spread around any more brain-fry now. Thank you Chris, and all. John Butler (Govinda) govinda.webdnat...@gmail.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Bastien, I had tried it with the parantheses around the date for the distinct. I tried again just now. Same result. But that's ok. I am onto the next step now. Niel, Jack, I got your fix working. It shows me that I am still so new; I own yet so little mastery of MySQL. Nisse, I see what you are suggesting. It seems I can go that route too. I have much to learn in every direction, so for right now anyway I am thinking to pursue the stream of thought started with what Niel and Jack just gave me. I do need data from the other columns too, and not just the date extracted from that timestamp field, ...and I need to count # of records in other tables that have the same unique date as the list of unique dates I just found in my first table, etc. .. so my thought is to want to do nested query(ies), where: *within* the while loop of the first recordset (which is now successfully returning just rows with unique dates), I do other query(ies) which will (in their own code block) find all rows of the date we are iterating.. so I can, for example, count number of records for each unique date, do math/statistics, etc. I need to play with everything before asking for more detailed help; I am just now asking if you think I am on the right track with my thinking - as I just mentioned in the sentence above this one? Thanks everyone! -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
.. so my thought is to want to do nested query(ies), where: *within* the while loop of the first recordset (which is now successfully returning just rows with unique dates), I do other query(ies) which will (in their own code block) find all rows of the date we are iterating.. so I can, for example, count number of records for each unique date, do math/statistics, etc. I had to do something similar in code of my own a little while ago, and got some very good guidance on Stack Overflow. Here's the thread, you might find it helpful: http://stackoverflow.com/questions/946214/one-sql-query-or-many-in-a-loop The user whose answer is marked as the correct one (Quassnoi) also writes a helpful blog on SQL. You should be able to find the blog by clicking on the username. Ben Thanks Ben. And yes Jack, ..I was attracted to the nested query as that required less new SQL ground to learn right now while I am expected to produce! But as that user (and others in that thread you gave, Ben) said, better to learn to do things the right way. So I need to read/learn more MySQL. Can you guys point me to where in the mysql docs I should be burying myself? Here's what I am trying to do: I have a table created by this: $SQL=CREATE TABLE t7solar_landing (solarLandingDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarLandingDir TINYTEXT,solarLandingIP TINYTEXT); and other tables too, like this: $SQL=CREATE TABLE aw_7solar_confirm (solarAwConfDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarAwConfIP TINYTEXT); and this: $SQL=CREATE TABLE aw_7solar_aw (solarAWDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarAWIP TINYTEXT,solarAWfm_email TINYTEXT,solarAWfm_meta_adtracking TINYTEXT, ... (plus more columns); I need to query these 3 tables (in one query! ;-) ...to return to me: one iteration of a while loop... ...which will echo: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/ tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... ...*per unique DATE* found in the 'solarLandingDateTime' column of the 1st (t7solar_landing) table. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Hi all
I'm translating some code from another server-side language into PHP,
and I need something that 'summarizes' results found from a MySQL
SELECT. I.e. -
$foundTrackingRows=mysql_query(SELECT...
while ($TrackingRow = mysql_fetch_object($foundTrackingRows)) {...
..such that the while loop only loops *ONCE per unique _date_ found
(regardless of the hour/min./sec.)* in my column which is of type
TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
PRIMARY KEY
For example, if I have column values like these:
2009-08-01 07:01:00
2009-07-30 18:16:37
2009-07-30 17:49:06
2009-07-27 17:35:52
2009-07-27 17:24:21
2009-07-27 17:23:03
..then my while { loop would only fire 3 times.
I do my RTFM; can someone just give me a good point in the right
direction.
Thanks!
John Butler/(Govinda)
govinda.webdnat...@gmail.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
You need to do this on the mysql side, not in php - php can't summarize the data before processing it, so you need to use something like the date() function in mysql on your timestamp column. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date without knowing your original query it's hard to give an example, but: select distinct date(timestamp_column) from table; Thanks Chris, I am getting closer, but so far it is not iterating only once per unique 'date part of the datetime expression', it is returning all the rows in the table, including those with the very same date but different time in the value of the 'solarLandingDateTime' column. There is not alot of discussion in the mysql docs that I saw about how to work with DISTINCT. I need to grab data out of the 3 columns: solarLandingIP, solarLandingDir, solarLandingDateTime (this part of my SELECT is working). This is what I have: $foundTrackingRows=mysql_query(SELECT DISTINCT DATE(solarLandingDateTime) solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); -Govinda
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
On Sun, Aug 2, 2009 at 8:02 PM,
Govindagovinda.webdnat...@gmail.com wrote:
You need to do this on the mysql side, not in php - php can't
summarize
the data before processing it, so you need to use something like
the date()
function in mysql on your timestamp column.
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date
without knowing your original query it's hard to give an example,
but:
select distinct date(timestamp_column) from table;
Thanks Chris,
I am getting closer, but so far it is not iterating only once per
unique
'date part of the datetime expression', it is returning all the
rows in the
table, including those with the very same date but different time
in the
value of the 'solarLandingDateTime' column. There is not alot of
discussion
in the mysql docs that I saw about how to work with DISTINCT. I
need to
grab data out of the 3 columns: solarLandingIP, solarLandingDir,
solarLandingDateTime (this part of my SELECT is working).
This is what I have:
$foundTrackingRows=mysql_query(SELECT DISTINCT
DATE(solarLandingDateTime)
solarLandingIP, solarLandingDir, solarLandingDateTime FROM .
$whichTable.
ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or
die(query
failed: .mysql_error());
-Govinda
try
foundTrackingRows=mysql_query(SELECT DISTINCT
DATE_FORMAT('%Y-%m,%d',solarLandingDateTime)
solarLandingIP, solarLandingDir, solarLandingDateTime FROM .
$whichTable.
ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query
failed: .mysql_error());
Bastien,
I just did try that. I also tried this, (thinking there was a typo in
your version, in the DATE_FORMAT formatting string), :
$foundTrackingRows=mysql_query(SELECT DISTINCT DATE_FORMAT('%Y-%m-
%d',solarLandingDateTime) solarLandingIP, solarLandingDir,
solarLandingDateTime FROM .$whichTable. ORDER BY
solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed:
.mysql_error());
still no luck.
-Govinda
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
What does the resultset look like? I am getting every record in the table back
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Oops, forgot to mention that with the alias you can change the ORDER BY clause to use the aliased column data: ORDER BY solarLandingDate DESC this will only use the returned data instead of the entire column. If you are aliasing a column it is better to use the optional AS keyword to avoid confusion. MySQL's DATE function returns dates formatted as '-MM-DD' so DATE_FORMAT is not needed here. Niel, Bastien, thanks for your efforts to lead me to understanding this! I tried everything you both suggested. Ideally I would have some clear docs that outline the syntax for me, for such an example as I need.. and I would be able to check my code myself. Meanwhile, In every case, I just get every record in the table back as a result. So then I thought, try and make even a *simple* DISTINCT work, and then move on to the date thing... so I try this: //$foundTrackingRows=mysql_query(SELECT DISTINCT solarLandingDir, solarLandingIP, solarLandingDir, solarLandingDateTime FROM . $whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); In all the records in this table, there are only 3 possible values in the 'solarLandingDir' column (TINYTEXT): diysolar solar_hm (null) but I still get all the records back, with each distinct 'solarLandingDir' column value represented several times. So something really basic is missing in my understanding/code. Can you see what it is? -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] html input element rendering html entities
On Jul 16, 2009, at 12:00 PM, Troy Oltmanns wrote: Hey guys and gals, I have a little tricky situation so I will explain as best as I can. I've got a form that loads and saves product data to mysql. In some fields like the heading and subhead we use special characters like Omega, Registered Trademark, and Ampersand. When putting the data in I will encode them as Omega;, etc. Once saved, all is good, everything goes ok and will display correctly in the user interface. However, if I go back to edit that product, in the html input element, it will actually display the character rather than the entity code (although when checking the source, the html entity is coded correctly). Then when it is saved, it saves as the illegal character and will serve up a few funky characters in the user interface and in the database. I have the code stripping out tags and I tried to use the mysql_real_escape_string to no avail. Anyone have experience with this kind of thing? PHP Version 5.1.6*MySQL 5.0.19* Govinda govinda.webdnat...@gmail.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] html input element rendering html entities
I've got a form that loads and saves product data to mysql. In some fields like the heading and subhead we use special characters like Omega, Registered Trademark, and Ampersand. When putting the data in I will encode them as Omega;, etc. Once saved, all is good, everything goes ok and will display correctly in the user interface. However, if I go back to edit that product, in the html input element, it will actually display the character rather than the entity code (although when checking the source, the html entity is coded correctly). Then when it is saved, it saves as the illegal character I am newbie here, but isn't it that you just need to save at this ^^ point the same way that you saved the data in the first place? If it worked the first time, why not now? and will serve up a few funky characters in the user interface and in the database. I have the code stripping out tags and I tried to use the mysql_real_escape_string to no avail. Anyone have experience with this kind of thing? PHP Version 5.1.6*MySQL 5.0.19* Govinda govinda.webdnat...@gmail.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] html input element rendering html entities
I've got a form that loads and saves product data to mysql. In some fields like the heading and subhead we use special characters like Omega, Registered Trademark, and Ampersand. When putting the data in I will encode them as Omega;, etc. Once saved, all is good, everything goes ok and will display correctly in the user interface. However, if I go back to edit that product, in the html input element, it will actually display the character rather than the entity code (although when checking the source, the html entity is coded correctly). Then when it is saved, it saves as the illegal character I am newbie here, but isn't it that you just need to save at this ^^ point the same way that you saved the data in the first place? If it worked the first time, why not now? and will serve up a few funky characters in the user interface and in the database. I have the code stripping out tags and I tried to use the mysql_real_escape_string to no avail. Anyone have experience with this kind of thing? PHP Version 5.1.6*MySQL 5.0.19* That's the thing, it is being saved the same way, it's that when the page loads the data the input element automatically changes the display to the special character, and when it saves, it saves as that character. then you just need to encode that form input value coming from the db back into the amp; entity, etc. AS YOU WRITE IT into the form input value HTML. I suspect there must be a built-in function just for this purpose. Someone can probably say what that is without looking (unlike me). But I did just look and how about this: htmlspecialchars ? -G -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] general theory behind 'primary_key' 'auto_increment' flags, and ON DUPLICATE KEY UPDATE ?
Reading docs.. I want to be sure of things... have your verification of my understanding(s)- Does setting a mysql db column (say myPrimarykeyID) with the 'primary_key' 'auto_increment' flags totally negate the logic for ever having to use ON DUPLICATE KEY UPDATE in an INSERT? Or is it that I can do an INSERT with SET myPrimarykeyID=someAlreadyExistingVal, a=1, b=2 ON DUPLICATE KEY UPDATE a=edited1, b=edited2 ... I am trying to learn/work out the mysql/php code that will allow me to either create *or* edit a record, in one fell swoop. Thanks for any insight! -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] general theory behind 'primary_key' 'auto_increment' flags, and ON DUPLICATE KEY UPDATE ?
On Jul 15, 2009, at 7:37 AM, Bastien Koert wrote: On Wed, Jul 15, 2009 at 9:21 AM, Govindagovinda.webdnat...@gmail.com wrote: Reading docs.. I want to be sure of things... have your verification of my understanding(s)- Does setting a mysql db column (say myPrimarykeyID) with the 'primary_key' 'auto_increment' flags totally negate the logic for ever having to use ON DUPLICATE KEY UPDATE in an INSERT? Or is it that I can do an INSERT with SET myPrimarykeyID=someAlreadyExistingVal, a=1, b=2 ON DUPLICATE KEY UPDATE a=edited1, b=edited2 ... I am trying to learn/work out the mysql/php code that will allow me to either create *or* edit a record, in one fell swoop. Thanks for any insight! -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php If that is the goal use the REPLACE INTO sql syntax You really don't want to allow duplicates in primary keys, that completely negates the usefulness of that key well isn't it that in order for the REPLACE INTO to replace the old row I have to try and replace a row with an already existing uniqueID? The same thing I would do to cause the INSERT.. SET .. ON DUPLICATE KEY UPDATE to kick in? Don't both methods equally effectively 'prevent duplicate primary keys from occurring'? -G
[PHP-DB] trying to dump out table info
Hi all
I am not sure why this is failing/giving error. I am quite new at PHP/
Mysql, and apologize for what will undoubtedly end up being a trivial
error on my part.
Here is my code:
$db_billing=mysql_connect(localhost,metheuser,mypass,billing);
if (!$db_billing) { die('Could not connect: ' . mysql_error()); }
$sql = SHOW TABLES;
$result = mysql_query($sql);
foreach(mysql_fetch_assoc($result) as $k = $v) { //line 62
$ssql = DESCRIBE .mysql_real_escape_string($v);
$rresult = mysql_query($ssql);
echo b.$k./b:br /\n;
echo pre\n;
print_r(mysql_fetch_assoc($rresult));
echo /pre\n;
echo br /\n;
}
which is giving this error:
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL
result resource in /home/meee/public_html/somedir/test.php on line 62
I read about:
-mysql_fetch_assoc
-mysql_query
-SHOW TABLES
but do not see why this should be failing. Why isn't $result a '
valid MySQL result resource'?
-G
Govinda
govinda.webdnat...@gmail.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] trying to dump out table info
On Jul 13, 2009, at 2:58 PM, Daniel Brown wrote:
On Mon, Jul 13, 2009 at 16:53, Govindagovinda.webdnat...@gmail.com
wrote:
Hi all
I am not sure why this is failing/giving error. I am quite new at
PHP/Mysql, and apologize for what will undoubtedly end up being a
trivial
error on my part.
Hey, I recognize that code ;-P
:-)
Here is the gifted code:
$db_billing=mysql_connect(localhost,metheuser,mypass,billing);
if (!$db_billing) { die('Could not connect: ' . mysql_error()); }
$sql = SHOW TABLES;
$result = mysql_query($sql) or die(mysql_error());
Note the addition to the $result definition. This will cause the
script to exit with the MySQL error message (if any) if the issue is
in the query.
when I make that change, it returns this:
No database selected
so I try to modify like this:
$db_billing=mysql_connect(localhost,metheuser,mypass,billing);
if (!$db_billing) { die('Could not connect: ' . mysql_error()); }
$sql = SHOW TABLES FROM billing LIKE 'myTable';
$result = mysql_query($sql) or die(mysql_error());
foreach(mysql_fetch_assoc($result) as $k = $v) { //line 64
//{DESCRIBE | DESC} tbl_name [col_name | wild]
//$ssql = DESCRIBE .mysql_real_escape_string($v);
$ssql = DESCRIBE myTable .mysql_real_escape_string($v);
$rresult = mysql_query($ssql);
echo b.$k./b:br /\n;
echo pre\n;
print_r(mysql_fetch_assoc($rresult)); //line 71
echo /pre\n;
echo br /\n;
}
and now the script returns this:
Tables_in_billing (myTable):
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL
result resource in /home/meee/public_html/somedir/test.php on line 72
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] trying to dump out table info
On Jul 13, 2009, at 4:40 PM, Daniel Brown wrote:
On Mon, Jul 13, 2009 at 17:24, Govindagovinda.webdnat...@gmail.com
wrote:
No database selected
You're getting warmer
How would you select a MySQL database in PHP? (Hint: there's a
built-in function.)
To try and follow your lead and focus just on what you are saying, to
be rid of that first error that said No database selected,
I changed the code back to this:
$db_billing=mysql_connect(localhost,metheuser,mypass,billing);
if (!$db_billing) { die('Could not connect: ' . mysql_error()); }
//$sql = SHOW TABLES FROM billing LIKE 'mytable';
$sql = SHOW TABLES; //line 237
//$result = mysql_query($sql);
$result = mysql_query($sql,$db_billing) or die(mysql_error());
//$result = $db_billing-query($sql) or die(mysql_error()); // line
241
foreach(mysql_fetch_assoc($result) as $k = $v) { //line 243
//{DESCRIBE | DESC} tbl_name [col_name | wild]
$ssql = DESCRIBE .mysql_real_escape_string($v);
//$ssql = DESCRIBE mytable .mysql_real_escape_string($v); //line 246
$rresult = mysql_query($ssql);
echo b.$k./b:br /\n;
echo pre\n;
print_r(mysql_fetch_assoc($rresult)); //line 250
echo /pre\n;
echo br /\n;
}
The question is why does line 240 think that no db is selected?!
I read the docs for mysql_query and it says:
resource mysql_query ( string $query [, resource $link_identifier ] )
so that 2nd param is optional; If the link identifier is not
specified, the last link opened by mysql_connect() is assumed.
But anyway I stuck it in there too, to test, and I get the same
result, i.e. No database selected.
I tried line 241 too. No luck.
So I am really stumped (lame as that feels/sounds). Line 237 is just
a string assignment. Line 240 *IS* being handed the $link to use,
which AFAIK means a reference to the database as well as the table, so
why the error?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] simple INSERT statement with placeholders failing. I don't understand why.
this: $q=mysqli_query($db_billing,'INSERT INTO billing (clientFname, clientLname) VALUES (?,?)', array($defaultsFormVars[clientFname], $defaultsFormVars[clientLname])); is giving Warning: mysqli::query() expects parameter 2 to be long, array given in /home/meee/public_html/somedir/test.php on line 71 a long?! We need values for 2 text/BLOB columns.. an array seems appropriate to me. It was with PEAR DB. WHy is it asking for a long? it was working fine with PEAR DB before I turned that off to try and learn better what was under PEAR. I found http://dev.mysql.com/doc/refman/5.1/en/insert.html but there is no example of using placeholders there, so I can't determine how to alter this example (which I learned from a book that just breezes through with only the one quick PEAR DB example.) If someone can even just point me to the right docs for this, I'd be grateful. thanks! -G -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] trying to dump out table info
On Jul 13, 2009, at 5:42 PM, Daniel Brown wrote:
On Mon, Jul 13, 2009 at 19:28, Govindagovinda.webdnat...@gmail.com
wrote:
$db_billing=mysql_connect(localhost,metheuser,mypass,billing);
if (!$db_billing) { die('Could not connect: ' .
mysql_error()); }
if(!mysql_select_db('dbname',$db_billing))
die(mysql_error());
//$sql = SHOW TABLES FROM billing LIKE 'mytable';
$sql = SHOW TABLES; //line 237
The fourth parameter you have in mysql_connect() only evaluates
within the engine as a boolean True statement. RTFM to see why. ;-P
Dan, I am really making the effort.. despite how it may look.
I read about mysql_connect again and see that 4th param is optional.
seemingly intended for when we want to force a new link and not just
use one that was already opened. Well I am just at this point trying
to use a first link, so I would assume I need to say what db I am
trying to talk to. Too bad the docs do not give a single example
where the 4th param is being used. Anyway I see in the function def.
that that 4th is a boolean. That does not make sense to me since I
think it should be a string (the name of the database). Anyway it was
working with PEAR DB with this:
$db_billing=DB::connect('mysql://metheuser:myp...@localhost/billing');
and still does seem to be connecting ok with this now (without pear db)-
$db_billing=mysql_connect(localhost,metheuser,mypass,billing);
...no error arises from the next line-
if (!$db_billing) { die('Could not connect: ' . mysql_error()); }
...so we must be talking to the db, right?
And to my real question, how can I get mysql_query what it needs in
this line:
$result = mysql_query($sql) or die(mysql_error());
I learn the best by *seeing* example code. I wish I could see a
working example.
-G
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Extensions on SuSE
Does anyone know how to get the _binary_ extensions for PHP installed with SuSE 7.2 ? I think it needs to recompile... But I wanted a easier way to solve it... Thanks, Ridai Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
