Re: [PHP-DB] First and Last ID of a table
SELECT MIN(id), MAX(id) FROM mytable :) Hope that helps! -TG = = = Original message = = = I have a table where I need to figure out the very first ID and the very last ID, so here is what I wrote: $first_query = SELECT id FROM mytable ORDER BY id LIMIT 1; $first_result = mysql_query($first_query,$con); $first_id = mysql_result($first_result,0,'id'); $last_query = SELECT id FROM mytable ORDER BY id DESC LIMIT 1; $last_result = mysql_query($last_query,$con); $last_id = mysql_result($last_result,0,'id'); I'm just wondering if there was any way to do this more efficiently, like with one query instead of two. Or is this about as simple as I can do it? Thanks. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada College www.wnc.edu 775-445-3326 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] First and Last ID of a table
Sounds about right... you can also do something like this (syntax should be right): SELECT MIN(id) as minid, MAX(id) as maxid FROM mytable $array['minid'] and $array['maxid'] Basically it's going to be whatever the heading of that column is. Using as gives it an alias for less ugly headings. If you did SELECT COUNT(Qty) FROM SomeTable Then you might get: $array['Count of Qty'] or something goofy like that. I forget the exact circumstances but there's times you get goofy stuff like that. If you run the SQL through some DB client (like mysql's command line stuff or I use WinSQL Lite in Windows to connect to our MySQL database across the network) you can usually see what the heading name is going to end up being, if you don't explicitly set it with an AS clause. AS also works on table names: SELECT l.LeadID, ld.FirstName FROM Leads as l, LeadData as ld WHERE l.LeadID = ld.LeadID (actually a lot of the time you can leave out the as and just do Leads l) Fun times.. -TG = = = Original message = = = [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] SELECT MIN(id), MAX(id) FROM mytable As an aside, is you are using associative arrays, be sure to use the following keys: $array['MIN(id)'] and $array['MAX(id)'] Just something I figured out recently :) Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql statement - complex order by
Try this: SELECT * FROM productgroup WHERE groupid = $productid ORDER BY label = 'Cats' DESC, title The test SQL I did to make sure I understood it was this (against our Users table): select * from users order by first = 'Bob' DESC, first, last It put all the Bobs first, sorting them by first/last, then put everyone else after the Bobs sorted by first/last. If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats' at the bottom of the list. Also refer to the user comments here: http://dev.mysql.com/doc/refman/4.1/en/sorting-rows.html good luck! -TG = = = Original message = = = SELECT * FROM productgroup WHERE groupid = $productid AND label = 'Cats' ORDER BY title SELECT * FROM productgroup WHERE groupid = $productid AND label != 'Cats' ORDER BY label,title I'd like to find a way to combine these 2 statements. I want to list out all the products, ordered by title but listing out all the Cats products first. Any way to do that without having separate statements? Thanks... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Search for whole words in MySQL 3.23
I suck at regex, but looks like 3.23 may actually support it. And most regex implementations have a word boundary code for doing exactly what you're talking about. Referring to this page: http://dev.mysql.com/doc/refman/4.1/en/regexp.html I found this example.. hopefully it'll help you (and work ok in 3.23): [[::]], [[::]] These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_). mysql SELECT 'a word a' REGEXP '[[::]]word[[::]]'; - 1 mysql SELECT 'a xword a' REGEXP '[[::]]word[[::]]'; - 0 -TG = = = Original message = = = Hi all, I'm setting up a database search on a site, where I will essentially be BOOLEAN searching about 6 database fields. Unfortunately, the database I'm using is MySQL version 3.23 (completely out of my control), so I cannot use IN BOOLEAN MODE to simplify the process on my end. I've been able to put together a function that does almost everything that I want, but I'm running into a problem - searching for whole words that may or may not be in the beginning or end of the database field. For example, syntax like this: ...WHERE `field` LIKE '%searchterm%'... has the limitation that if the search term is 'car', it will also bring back 'scar', 'cartoon', etc. While if the syntax is: ...WHERE `field` LIKE '% searchterm %'... (with spaces) it misses words that are the first or last words of the field, or words that are at the end of sentences, before commas, etc. I've looked around and haven't been able to find a way to search only for complete words within a block of text. Any help? Thanks in advance, Zeth ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Values in a date field
Assuming you're using MySQL, try MONTH(). As in: SELECT MONTH(SomeDateField) FROM SomeTable http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html If this isn't a database question, but a general PHP question, try this: // For numeric month without leading zero, use n echo date(n, strtotime($subscription_begins)); http://us3.php.net/manual/en/function.date.php -TG = = = Original message = = = I have a $subscription_begins variable. It the date type. -MM-DD How may I find out the month value (1 to 12) of this variable? Ron ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Query - Using variable from another SQL Query
Try this as your SQL. It should give you all the results, then you can use PHP to sort it all out. SELECT * FROM egw_cal WHERE cal_category='501' and cal_id in (SELECT cal_id FROM egw_cal_dates where cal_start $tstamp) -TG = = = Original message = = = Hello Everyone Got a simple / stupid question. Worked on this all night. I'm over looking something very basic here. The query event_time brings back the calendar id for each event that is pending in the future. ie 12, 13, 14, 26 (There could be 100 of them out there) The second query events needs to meet both reqirements. 1 - cal_category='501' 2 - cal_id= a number from the event_time query I think i need to do a loop inside of a loop Thanks... Matt Here is my code: ?php $todays_year = date(Y); $todays_month = date(m); $todays_day = date(d); $tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year); $event_time = mysql_query(SELECT cal_id FROM egw_cal_dates where cal_start $tstamp, $db); $events = mysql_query(SELECT * FROM egw_cal WHERE cal_category='501' and cal_id='$event_time'\n, $db); if ($event = mysql_fetch_array($events)) echo center\n; echo HR\n; do echo BFont Size='10'$event[cal_title]nbsp;nbsp;nbsp;nbsp;-nbsp;nbsp;nbsp;$event[cal_location]/B/Font\n; echo BR\n; echo $event[cal_description]; echo BR\n; echo HR\n; while ($event = mysql_fetch_array($events)); else echo No Public Events Are Currently Scheduled...; ? ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] 2 queries -- 1 array
Not sure if this is exactly what you're looking for, Matt, but I think it is.
?php
$query = SELECT t1.NAME, t1.BUSINESS, t1.ADDRESS, t1.CITY, t1.PHONE,
t2.CONTACT_NAME, t2.CONTACT_VALUE FROM Table1 as t1, Table2 as t2 WHERE t1.ID =
t2.CONTACT_ID
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
// to make it easier to read, I'll assign the values to variables for this
demonstration
$name = $row['NAME'];
$business = $row['BUSINESS'];
$address = $row['ADDRESS'];
$city = $row['CITY'];
$phone = $row['PHONE'];
$contactname = $row['CONTACT_NAME'];
$contactvalue = $row['CONTACT_VALUE'];
$bigarray[$name][$contactname] = $contactvalue;
}
$JoeBrownPrevsys = $bigarray['Joe Brown']['Prevsys'];
?
If that's not right, please re-describe the problem and let's see what we can
figure out.
If you have questions about what/how/why/etc... feel free to ask.
-TG
= = = Original message = = =
Hello,
I need to query two different tables and create one BIG array.
I can query both tables, but can't find a way to combine the results in one
array.
The problem comes in with the second table. It has a weird layout.
The second table only has four fields. They are: contact_id, contact_owner,
contact_name, and contact_value
a.. The 'contact_id' needs to be linked to 'id' field from the first table.
b.. The contact_name is a field I need to use to reference what i'm looking
for.
example... contact_name HAS values of RANO, PrevSys,
Supplier, Dynamic
each of them has the same contact_id different
contact_names with different contact_values
To query the second table I do something like this:
SELECT contact_value FROM egw_addressbook_extra WHERE contact_name='Supplier'
and contact_id=' XXX'
The xxx is the ID from the first table.
-
Table 1:
IDNAMEBUSINESS
ADDRESSCITY PHONE
1 John SmithSmith's Phone Co. 100 Main
St Someplace 555-
2 Joe Brown Brown's Shoe Store105
Grant St.This City 555-0001
Table 2:
CONTACT_IDCONTACT_NAME
CONTACT_VALUECONTACT_OWNER
1 PrevSys
NONE
4
1 RANO
100102
4
1 Supplier
TrueValue
4
1 Dynamic
192.168.0.0
4
2 PrevSys
OLD POS SYS
4
2 RANO
100105
4
2 Supplier
Orgill
4
2 Dynamic
192.168.100.0
4
I want to query both tables and create a array. The extra fields from table
two need to have their own field in the array.
I need to call those extra fields in the array by the contact_name field...
example $myrow[supplier] $myrow[PrepSys] $myrow[name]
I have tried alot of stuff to get this done, but i think i was very off task.
Some help/guide is needed I'm new
Thanks...
Matt
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] 2 queries -- 1 array
Ok, in your original message you said something about using the name as
dominant key (or whatever you want to call it). All the SQL does is gather all
the data together in an associated fashion. You can use the array you build to
organize the data. I'm still not 100% sure what you're doing, so I can't say
this is the best way, but let's look at the exampe I gave and change it to
hopefully suit your last description:
$name = $row['NAME'];
$business = $row['BUSINESS'];
$address = $row['ADDRESS'];
$city = $row['CITY'];
$phone = $row['PHONE'];
$contactname = $row['CONTACT_NAME'];
$contactvalue = $row['CONTACT_VALUE'];
// $bigarray[$name][$contactname] = $contactvalue;
$bigarray[$contactname][$name] = $contactvalue;
foreach ($bigarray['Supplier'] as $name = $value) {
echo Supplier: $name has CONTACT_VALUE = $valuebr\n;
}
There may be an elegant way to do what you want in SQL too, I'm just not fully
groking the situation I think.
-TG
$name = $row['NAME'];
$business = $row['BUSINESS'];
$address = $row['ADDRESS'];
$city = $row['CITY'];
$phone = $row['PHONE'];
$contactname = $row['CONTACT_NAME'];
$contactvalue = $row['CONTACT_VALUE'];
$bigarray[$name][$contactname] = $contactvalue;
= = = Original message = = =
yeah..that is what i'm trying to do, but
I need to query WHERE contact_name='Supplier' and bring back the value for
contact_value
query WHERE contact_name='RANO' and bring back the value for
contact_value
query WHERE contact_name='Dynamic' and bring back the value
for contact_value
query WHERE contact_name='PrevSys' and bring back the value
for contact_value
Which are all different rows in the second table
with the same ID field
thats how they are linked to the first table
thanks...
Matt
- Original Message -
From: [EMAIL PROTECTED]
To: php-db@lists.php.net
Cc: [EMAIL PROTECTED]
Sent: Friday, January 12, 2007 4:26 PM
Subject: Re: [PHP-DB] 2 queries -- 1 array
Not sure if this is exactly what you're looking for, Matt, but I think it
is.
?php
$query = SELECT t1.NAME, t1.BUSINESS, t1.ADDRESS, t1.CITY, t1.PHONE,
t2.CONTACT_NAME, t2.CONTACT_VALUE FROM Table1 as t1, Table2 as t2 WHERE
t1.ID = t2.CONTACT_ID
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))
// to make it easier to read, I'll assign the values to variables for
this demonstration
$name = $row['NAME'];
$business = $row['BUSINESS'];
$address = $row['ADDRESS'];
$city = $row['CITY'];
$phone = $row['PHONE'];
$contactname = $row['CONTACT_NAME'];
$contactvalue = $row['CONTACT_VALUE'];
$bigarray[$name][$contactname] = $contactvalue;
$JoeBrownPrevsys = $bigarray['Joe Brown']['Prevsys'];
?
If that's not right, please re-describe the problem and let's see what we
can figure out.
If you have questions about what/how/why/etc... feel free to ask.
-TG
= = = Original message = = =
Hello,
I need to query two different tables and create one BIG array.
I can query both tables, but can't find a way to combine the results in
one array.
The problem comes in with the second table. It has a weird layout.
The second table only has four fields. They are: contact_id,
contact_owner, contact_name, and contact_value
a.. The 'contact_id' needs to be linked to 'id' field from the first
table.
b.. The contact_name is a field I need to use to reference what i'm
looking for.
example... contact_name HAS values of RANO, PrevSys,
Supplier, Dynamic
each of them has the same contact_id different
contact_names with different contact_values
To query the second table I do something like this:
SELECT contact_value FROM egw_addressbook_extra WHERE
contact_name='Supplier' and contact_id=' XXX'
The xxx is the ID from the first table.
-
Table 1:
IDNAMEBUSINESS
ADDRESSCITY PHONE
1 John SmithSmith's Phone Co. 100
Main St Someplace 555-
2 Joe Brown Brown's Shoe Store105
Grant St.This City 555-0001
Table 2:
CONTACT_IDCONTACT_NAME
CONTACT_VALUECONTACT_OWNER
1 PrevSys
NONE4
1 RANO
100102 4
1 Supplier
TrueValue 4
1 Dynamic
192.168.0.0 4
2
Re: [PHP-DB] Filter array results... no copies
If all you want is a unique list of adr_one_region codes, then use the DISTINCT keyword in your SQL: SELECT DISTINCT adr_one_region FROM egw_addressbook WHERE cat_id='8' ORDER BY adr_one_region -TG = = = Original message = = = Hello everyone I'm back working on the website again... I'm having lots of fun. I have a sql query that looks at one field in a database. (result2 query) Then i have mysql_fetch_array statement. I then use this array to print links on the page. Works fine except I don't want duplicate links. It creates links for the states. If we have four customers from Pennsylvania. This current process gives me four different PA links on my page. I need a statement that says...if its already printed...don't print it... Thanks... Here is my code: html body ?php $db = mysql_connect(HOST, USERNAME, PASSWORD); mysql_select_db(DATABASE,$db); if ($_GET[area]==) $master = mysql_query(SELECT * FROM egw_addressbook WHERE cat_id='8' ORDER BY org_name, $db); else $master = mysql_query(SELECT * FROM egw_addressbook WHERE cat_id='8' and adr_one_region='$_GET[area]' ORDER BY org_name, $db); $result2 = mysql_query(SELECT adr_one_region FROM egw_addressbook WHERE cat_id='8' ORDER BY adr_one_region, $db); if ($area = mysql_fetch_array($result2)) echo Sort by State: ; do echo a href='index.php?area=$area[adr_one_region]'$area[adr_one_region]/a\n; echo - ; while ($area = mysql_fetch_array($result2)); echo a href='index.php?area='ALL/a\n; else echo ERROR; if ($myrow = mysql_fetch_array($master)) echo CENTER\n; echo table border=0\n; echo img src=file.jpg width='611' height='136'\n; echo BR\n; echo BR\n; do printf(tr td/tdtdb%s/b/tdtd/tdtd/tdtd%s/td/tr\n, $myrow[org_name], $myrow[fn]); printf(tr td/tdtd%s/tdtd/tdtd/tdtd%s/td/tr\n, $myrow[adr_one_street], $myrow[tel_work]); printf(tr td/tdtd%s, %s %s/tdtd/tdtd/tdtd/tdtd/td/tr\n, $myrow[adr_one_locality], $myrow[adr_one_region], $myrow[adr_one_postalcode]); while ($myrow = mysql_fetch_array($master)); echo /table\n; echo /CENTER\n; else echo Sorry, no records were found!; ? /body /html ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Order By [blank]
This is a little weird looking, but should do the job. Remember that items in your 'order by' can be manipulated conditionally. In this case, I'm looking for NULL as well as '' (empty) and changing it to something that should come after all your normal alphabetical values, but it doesn't change what appears in your results. This only affects the sorting: select * from sometable order by if(ifnull(somecolumn, '') = '', '', somecolumn) Hope that helps. -TG = = = Original message = = = I have this column in mysql: A F D [ empty ] A C If I do an order by on that column, this is what I get: [ empty ] A A C D F What I would like is this: A A C D F [ empty ] Is there any way to achieve this in a single MySQL query? Using DESC in this case doesn't work, because while it puts the empty row in the last place, it does the rest as well. I could also do 2 queries where it calls it once in order WHERE !='', and then do another query to get the empty ones, but that seems a bit cumbersome. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Order By [blank]
This is a little weird looking, but should do the job. Remember that items in your 'order by' can be manipulated conditionally. In this case, I'm looking for NULL as well as '' (empty) and changing it to something that should come after all your normal alphabetical values, but it doesn't change what appears in your results. This only affects the sorting: select * from sometable order by if(ifnull(somecolumn, '') = '', '', somecolumn) Hope that helps. -TG = = = Original message = = = I have this column in mysql: A F D [ empty ] A C If I do an order by on that column, this is what I get: [ empty ] A A C D F What I would like is this: A A C D F [ empty ] Is there any way to achieve this in a single MySQL query? Using DESC in this case doesn't work, because while it puts the empty row in the last place, it does the rest as well. I could also do 2 queries where it calls it once in order WHERE !='', and then do another query to get the empty ones, but that seems a bit cumbersome. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Order By [blank]
You shouldn't have to do that. the IFNULL() handles all that. If the item is null, it returns an emptry string ''. If it's blank/empty, it returns an empty string. This is just used for the comparison = ''. This determines if it's empty or null and if so, returns 'ZZ', if not, it returns the unaltered value. And again, this returned value is only used for the sorting. The values you get from select * will be unaltered. Is this more efficient than doing two SELECTs and a UNION? I have no idea. But I like to keep things as clean as possible and in my reading and experience, letting the server handle an IFNULL() function should be quicker than doing four value checks (is null, is not null, = '' and != ''), collecting the values of two SELECTS then checking to see if it's able to UNION them together. Also, less code/typing typically means less chance of typos. BTW: In my example, realistically you could probably shorten the morphed value to ZZ or ZZZ unless you think you'll values in your database that will start with and get bumped lower on the sorting. -TG = = = Original message = = = In case the blank is a null or is really a blank: select * from blank where tchar_10 is not null and tchar_10 != '' union all select * from blank where tchar_10 is null or tchar_10 = '' - Dave On 12/21/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: This is a little weird looking, but should do the job. Remember that items in your 'order by' can be manipulated conditionally. In this case, I'm looking for NULL as well as '' (empty) and changing it to something that should come after all your normal alphabetical values, but it doesn't change what appears in your results. This only affects the sorting: select * from sometable order by if(ifnull(somecolumn, '') = '', '', somecolumn) Hope that helps. -TG = = = Original message = = = I have this column in mysql: A F D [ empty ] A C If I do an order by on that column, this is what I get: [ empty ] A A C D F What I would like is this: A A C D F [ empty ] Is there any way to achieve this in a single MySQL query? Using DESC in this case doesn't work, because while it puts the empty row in the last place, it does the rest as well. I could also do 2 queries where it calls it once in order WHERE !='', and then do another query to get the empty ones, but that seems a bit cumbersome. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MSSQL Server
I had some issues a couple years ago connecting to the MS SQL Server where I
was employed at the time.
I was probably just doing something wrong, but what I ended up using that
worked for me was using the ADODB database abstraction layer. Helped me
connect to MS SQL , Oracle and some other random data sources I had to from
time to time.
http://adodb.sourceforge.net/
Sorry I don't have time to look for good examples, but maybe the documentation
will help out some and get you on the right path.
-TG
= = = Original message = = =
I cannot connect to my MSSQL server.
I use the following connection string:
php:
--
$connection = mssql_connect http://php.net/mssql_connect('server','user',
'password') or die ('server connection failed');
$database = mssql_select_db http://php.net/mssql_select_db(database_name
, $connection) or die ('DB selection failed');
?
--
The MSSQL server is setup to use Windows and Server Authentication. I have a
*local* dummy account setup for testing and when using this account I can
connect with no problems, but when I try and use my domain/network account I
cannot connect at all. (my network account has full access to the server,
database and tables) I can even login to the SQL Management Studio using
Windows Authentication and my network account, but still cannot connect
using PHP.
Any ideas?
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] search result error message
Sorry, don't have time to test and noodle through why yours may or may not be
working, but I see some differences in how you're doing it and how we tend to
do it here.
After doing the connection and database selection, this is how we handle stuff
(simplified):
$query = select * from sometable;
$result = mysql_query($query);
$count = mysql_affected_rows();
while ($row = mysql_fetch_assoc($result)) {
// do some stuff
$address = $row['address']; // example of how to get/use data returned
}
Nothing inside the while construct should execute if you get zero results.
You really don't need to use the mysql_affected_rows() either unless you want
to test for zero results and display an error message, like I believe you
wanted to do.
-TG
= = = Original message = = =
Hi,
The below mentioned code works fine. Connects to the database, fetches the
result, and displays neatly in a table. If there is no data then it jumps to
the if condition and displays the error message. BUT if the 'if' condition
is running and no data is present the Table headings and the bottm of empty
table is still displayed above the error message. Can you please check and
share with me as in where exactly the logic is wrong.
-- -- Code ---
h2Your favorites search result/h2divYou are here: index.htm Home
Your favorites/div~~
div class=internalContentArea sgtable
~~~div class=tableHeadSub-category search result/div~~
~div
~table width='100%' id='table1' cellspacing=0theadtr
th scope=colShop name/th
/tr/theadtbody
?php
// database information
$host = 'xxx';
$user = 'xxx';
$password = 'xxx';
$dbName = 'xxx';
mysql_connect(localhost,$user,$password);
@mysql_select_db($dbName ) or die( Sorry But There Seems To Be A Problem
Connecting To The Database);
$query=SELECT * FROM shop;
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i $num)
$shopname=mysql_result($result,$i,shopname);
echo
tr
td$shopname/td
/tr;
$i++;
?/tbody/table index.htm Back to search div
/div
?
if ($num == 0)
echo
div
div class='alert'The search criteria you entered did not generate any
result, index.htm please try again ./div
~/div;
?
~
/div.
--
View this message in context:
http://www.nabble.com/search-result-error-message-tf2867391.html#a8014018
Sent from the Php - Database mailing list archive at Nabble.com.
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] running program after insert w/MySQL 4.1
cron or Windows Task Manager would work if you want to check the status of the data periodically, but I believe you can also use Access to hit a web address (it doesn't have to display anything) so you could, at time of insert, have Access activate a PHP script on your web server that does the database check. You could probably get the last inserted ID number as well and pass that to the PHP script if you want to check specific records right after they're inserted. -TG = = = Original message = = = I have an Access database connecting via ODBC to a local MySQL machine and doing inserts. It does these inserts throughout the day. After each insert, I need a PHP program run. Alas, MySQL 4.1 doesn't do triggers, etc., and upgrading to 5.x looks non-trivial. What else can I do? Is there any way to trigger something via the ODBC connection? A cron job? I'm open to suggestions. kind regards, bill ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Problem with list#2
You may get duplicate messages sometimes when someone responds directly to you as well as copying the message to the main mailing list. Like in this ccase, my To: field is the PHP DB list, and my CC: field is your personal email address. If that's not the issue, then try what Dave suggested and unsubscribe using one of your email addresses and see if that solves the problem. -TG On 7/7/06, Karl James [EMAIL PROTECTED] wrote: Team, I am still receiving duplicate emails. I check with my ISP and they tell me it is Not their problem. I tried to check the headers But I am not able to see anything that indicates What email address its being sent to because I have two of them. Can anyone help suggest on how to resolve this. I would send a screen shot but its not advise to In forums. Off list I can for sure if any one wants To see what's going on. Karl James (TheSaint) mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] www.theufl.com ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] LIMIT
Here are some thoughts on the couple of methods shown: 1. SELECT count(*) as MyCount FROM aTable # Fast query done on server size SELECT * FROM aTable LIMIT 5 # Fast limited data return query done server side 2. SELECT * FROM aTable # Potentially slow query, lots of data stored in PHP mysql_num_rows() # Quick PHP-side function to tell how many results returned 3. SELECT SQL_CALC_FOUND_ROWS * FROM $table LIMIT 5 # Fast server side query that does the same as SELECT count(*) and SELECT LIMIT 5 at the same time SELECT FOUND_ROWS() # Fast server side query Seems like #1 and #3 are your best options. Both are pretty easy on your system. #3 is definitely pretty slick, thanks to whoever originally posted that. But I'm wondering if it'll be fully compatible in the future. #1 is pretty SQL 101 type stuff, so it should remain functional and be usable on other database platforms as well. That's the one I'd go for, just for the sake of keeping things simple if nothing else. Seems like a waste to do two database queries, but as long as you're not closing and re-opening the connection in between, it should cause very minimal impact on your system. Sometimes two queries are better than one. -TG = = = Original message = = = I see they call mysql_query twice which doesn't look like I'm gaining anything over running the 2 queries: SELECT count(*) as MyCount FROM aTable SELECT * FROM aTable LIMIT 5 But I suppose I need to time it for my app. http://us3.php.net/mysql_num_rows mancini at nextcode dot org 14-Nov-2005 02:24 here is a really fast mysql_num_rows alternative that makes use of the SELECT FOUND_ROWS() MySQL function , it only reads a single row and it is really helpfull if you are counting multiple tables with thousands of rows ?php function get_rows ($table) $temp = mysql_query(SELECT SQL_CALC_FOUND_ROWS * FROM $table LIMIT 1); $result = mysql_query(SELECT FOUND_ROWS()); $total = mysql_fetch_row($result); return $total[0]; ? +++ alex dot feinberg 4t gm41l 28-Apr-2005 04:56 Re dzver at abv dot bg's note... I just ran some tests using MySQL Super Smack. Surprisingly, a SELECT * followed by a SELECT COUNT(*) actually was close in speed to a SELECT SQL_CALC_FOUND_ROWS * followed by a SELECT FOUND_ROWS(), but the SQL_CALC_FOUND_ROWS solution was still a bit faster. Perhaps it varies by table structure? Either way, it might be worth checking which is faster for your application. Regards, Dwight -Original Message- From: Dwight Altman [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 05, 2006 9:53 AM To: 'php-db@lists.php.net' Subject: RE: [PHP-DB] LIMIT So how do I get this information on the PHP side? mysql SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name - WHERE id 100 LIMIT 10; mysql SELECT FOUND_ROWS(); I count 2 queries above (unless there is some caching magic). Whatever. I was hoping to add another column (like FOUND_ROWS) to an existing query and be able to pull out the FOUND_ROWS when I loop over the result set. Something like: SELECT *, FOUND_ROWS FROM aTable LIMIT 5 But since that single query doesn't work, how do I apply the MySQL solution and extract it on the PHP side? http://dev.mysql.com/doc/refman/4.1/en/information-functions.html http://www.mysqlfreaks.com/statements/101.php Regards, Dwight -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Friday, June 30, 2006 9:46 AM To: php-db@lists.php.net Subject: Re: [PHP-DB] LIMIT If you're using MySQL then: SELECT SQL_CALC_FOUND_ROWS * FROM aTable LIMIT 5 SELECT FOUND_ROWS() It's in the mysql documentation under SELECT syntax I believe. Chris Dwight Altman wrote: Is there a way to get the number of rows that would have been returned had there not been a LIMIT clause in a SELECT statement? For example, if Query #1) SELECT * FROM aTable would normally return 100 rows. But Query #2) SELECT * FROM aTable LIMIT 5 will return 5 rows. Is there a way to find out that 100 rows would have been returned if there was no LIMIT clause, by using only Query #2 and maybe a PHP function on the $result? Regards, Dwight -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select distinct field won't return distinct value
Correct me if I'm wrong, but it sounds like you have something like this:
123 Joe
124 Joe
125 Sue
126 John
127 Joe
128 Frank
129 Sue
And you want to output something like:
Joe 123, 124, 127
Sue 125, 129
John 126
Frank 128
But what you're getting is:
Joe 123
Joe 124
..etc
You have two ways you can solve this:
1. Do two SQL queries:
SELECT DISTINCT cus_name FROM customers
while ($result) { // forgive the pseudo-code
SELECT cus_id FROM customers WHERE cus_name = $result['cus_name']
while ($result2) {
echo $output;
}
}
Or..
2. Collect data into an array and process 'distinctness' on output
SELECT cus_name, cus_id FROM customers
while ($result) {
$cus_arr[$cus_name][] = $cus_id;
}
foreach ($cus_arr as $cus_name = $cus_idarr) {
echo $cus_name as ids: . implode(, , $cusidarr) . br\n;
}
There may be some tricky ways in SQL to get the data the way you want it, but
ultimately it's not worth the bending over backwards for (do I remember right
that you can do it with crosstab queries? don't even know if MySQL will do
those properly). Easier just to do it with one of the methods above.
Good luck!
-TG
= = = Original message = = =
I want select distinct field and return value of that field, but I have
problem which is: select distinct returns duplicate value. eg, I wan select
distinct customer name and id from the customer table. one customer may have
different cus_ids since cus_ids are auto increment and depend on the
purchased items. so what I want is to select distinct customer name so that
I can print customer name and customer id once.
here is algorithm;
select distinct cus_name, cus_id from customers order by cus_name asc
While row is not empty do
echoa href=\page?cus=cus_id\costomer name/abr /;
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Automatically +1 every 30 mins to a value in the database.
An alternative to cron jobs or scheduled tasks (if they're not an option) is to have a marker somewhere (probably in your database) that records a timestamp for the last 'gold' update then when someone does get around to accessing the database through normal operations, have it figure out how many 30 minute periods have occurred between the timestamp and now and update the gold then. The cron job is preferrable but if you're looking for alternate ideas, there's one for ya. Good luck! -TG = = = Original message = = = Hi there. I've posted this up on both this list, and the mysql list as I'm not sure whether this is something that I'd need to do with the php or mysql. Basically, I am making an add-on to my small website which is a mini online game. Every user will have gold, and every 30mins I'd like their amount of gold to go up by 1 (or say a variable say $goldupdateamount). I'd like to know which would be the best way of doing this, and if there is a command in php which would achieve this. Regards, Alex. ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL/PHP Left Join Question
One thing I've done in situations like this is just load your returned DB data
into an array. Something like this would do:
$dvdarr[$result['call_number']]['Title'] = $result['title'];
$dvdarr[$result['call_number']]['Publisher'] = $result['publisher'];
$dvdarr[$result['call_number']]['Comments'][] = $result['comment'];
Put that in a loop of your results. Notice the [] on the comments. That'll
collect all your comments under the umbrella of 'Comments'.
Then when you go to do your output, you'd do something like this:
forach ($dvdarr as $callnumber = $dvddata) {
$title = $dvddata['Title'];
$publisher = $dvddata['Publisher'];
$comments = $dvddata['Comments'];
foreach ($comments as $comment) {
// do whatever
}
}
There are other ways to handle this.. this might be one of the easier ones (if
I'm understanding your problem correctly).
-TG
= = = Original message = = =
Hello, all. I don't know if this is a php-mysql question or just a
mysql, but here goes:
I have a list of DVDs that my library loans out, and I'd like to allow
people to add comments to each item. Since this list gets regenerated
periodically (it's extracted from another system, the library
catalog), there isn't a consistent ID in the dvd tables, so I'm using
the call number (which will look like DVD 2324) as the key. Anyhow, I
join the tables like this to get all the DVDs and all the comments
associated with the DVDs:
SELECT distinct dvds.title, dvds.publisher, dvds.publication_date,
dvds.call_number,
comment.id, comment.parent_id, comment.comment, comment.name
FROM dvds
LEFT JOIN comment
ON dvds.call_number=comment.parent_id
WHERE dvds.title LIKE 'A%'
ORDER BY dvds.title
With this, I'll get results like
DVD 101A.I. This movie rocked
DVD 101A.I. This Movie stunk
DVD 102Adaptation . . .
DVD 103After Hours . . .
When I loop in PHP through the records, of course, I want just the one
DVD with however many comments associated with it. Is it possible to
do this (i.e., screen out DVD dupes) in MySQL, or do I have to do it
in PHP?
If this is a dumb question, my humblest apologies, and I'd be
interested if there was a better way to handle this . . . .
Andrew
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Adding url to google
Sounds like you're asking how to programmatically add your web site to Google's search. I didn't dig very deep, but it doesn't look like Googles API provides for the ability to submit (only search) and by Google's addurl page (http://www.google.com/addurl/?continue=/addurl) it appears that they don't want you to be able to automate the adding of URLs or else they wouldn't be using CAPTCHA methods to prevent bots from spamming them. You may look into software like SubmitWolf or some of the others that automate submissions and try to figure out how they do it (if at all) but I'm guessing it's not going to really be feasible for you to do this... not easily at least. -TG = = = Original message = = = Hi all, I am developing the site using php and mysql. I have to add the url at google site through php code. If any one have idea about it, please help me. Regards Manoj ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Too stupid to UPDATE...
Don't have time to totally disect it, but you might change the if($num_rows) to if($num_rows 0) just to make sure. Also, try echoing out your SQL statement to check and make sure things like $table got a real value or other silly things. Lastly, I usually use single quotes for SQL statements.. in this case, it would simplify your PHP SQL statement by removing the necessity to escape all the double quotes \ If you echo the SQL and it looks ok, try running it manually through mysql's command line or via a program like WinSQL Lite on Windows to try to isolate if it's the query or your PHP. Good luck! -TG = = = Original message = = = I am trying to write a script to handle event registration. Most of the people that will be registering are already in the database, but not all, and those that are may not have current information. Here is my latest effort, or at least a snippet of it... Can anyone point out where I went stupid, or if there is a better way of doing this? THANKS!!! ===--- BEGIN SNIPPET ---=== $db = @mysql_select_db($dbname, $connection) or die(Couldn't Select Database.); $link = mysql_connect($server, $user, $pass); mysql_select_db($dbname, $link); $result = mysql_query(SELECT * FROM $table WHERE first_name='$first_name' AND hs_last_name='$hs_last_name' AND last_name='$last_name', $link); $num_rows = mysql_num_rows($result); if($num_rows) $sql = UPDATE $table SET first_name=\$first_name\, last_name=\$last_name\, hs_last_name=\$hs_last_name\, guest_name=\$guest_name\, street_address1=\$street_address1\, street_address2=\$street_address2\, city=\$city\, state=\$state\, zip=\$zip\, phone1=\$phone1\, phone2=\$phone2\, email_address=\$email_address\, number_attending=\$number_attending\, payment=\$payment\, registration_comments=\$registration_comments\, date_registered=\$today\ WHERE first_name=\$first_name\ AND last_name=\last_name\; $result = @mysql_query($sql,$connection) or die(Couldn't Execute Query.); else $sql = INSERT INTO $table (first_name, last_name, hs_last_name, guest_name, street_address1, street_address2, city, state, zip, phone1, phone2, email_address, number_attending, payment, registration_comments, date_registered) VALUES (\$first_name\, \$last_name\, \$hs_last_name\, \$guest_name\, \$street_address1\, \$street_address2\, \$city\, \$state\, \$zip\, \$phone1\, \$phone2\, \$email_address\, \$number_attending\, \$payment\, \$registration_comments\, \$today\) ; $result = @mysql_query($sql,$connection) or die(Couldn't Execute Query.); ===--- END SNIPPET ---=== ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Excel to CSV
The other response is half correct.. I havn't seen anything to read data from Excel with PHP (although it's technically possible, just kind of complicated) so the easiest solution is to use Excel and just have it load the file and save it in CSV format. The part that's not entirely correct is the PHP can't talk to Excel part. It's cumbersome, but PHP can use a COM connection to control Excel (or Word or MapPoint or Access or Outlook..etc). This means you'd have to have Excel loaded on the server that was running PHP, which may not be an option. For an example of PHP + COM with Excel, check out: http://www.php.net/manual/en/class.com.php Look down the page for the entry posted by flintjt at hotmail dot com If COM doesn't work for you and you don't mind getting your hands dirty, I believe you can get the Excel file format spec.. maybe through the OpenOffice sources.. and figure out how to read XLS files that way. The Excel Object Model might help you with the proper properties/methods/etc: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfexcelworkbookobject.asp Good luck. -TG = = = Original message = = = Dear All, Have you any script/example how to Save As Excel file to CSV using PHP? Thanks Regards, Anita ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Installing PEAR
I remember the quick install/test I did using PEAR and I kind of had a similar experience, but I figured out what the issue was for me. PEAR installed perfectly but doing the pear install thing where it pulls the package down and puts it where it needs to put it, I was doing what you were doing pear packagename and it wasn't working. What fixed it for me was adding the version number to the end of the name. Looks like PEAR DB is at 1.7.6, so try something like: pear install db1.7.6 But it also sounds like maybe you're not getting the pear install to work properly too. So maybe you need to fix something else before you get to this point. Just wanted to mention it to prevent a potential extra frustration for you. Back to work for me.. working late on a Friday sucks.. so forgive me if I'm not entirely helpful.. just wanted to pass that nugget along in case it was useful. -TG = = = Original message = = = I know this may be a bit off-topic, but I would like to use the PEAR library to implement some of my database functions. Unfortunately, the PEAR documentation is woefully incomplete and as a result i've been unable to install it properly. Here's the procedure i went through: PHP Version: 5.1.2 Apache Version: 2.0.55 MySQL Version: 5.0.18 Windows XP Pro 1. launch the go-pear batch file 2. select system-wide installation 3. use default install directories: (Seems fine to me since PHP is installed into C:\PHP) Installation Base ($prefix) C:\PHP Binaries DirectoryC:\PHP PHP code Directory ($php_dir) C:\PHP\pear Documentation Directory C:\PHP\pear\docs Data Directory C:\PHP\pear\data Tests DirectoryC:\PHP\pear\tests Name of Configuration FileC:\WINDOWS\pear.ini Path to CLI php.exe C:\PHP\. 4. The batch file tells me pear is installed 5. I add the environment variable via the PEAR_ENV.reg file 6. add the line require db.php and load to this message: Warning: require(DB.php) [function.require http://localhost/function.require ]: failed to open stream: No such file or directory in C:\wwwroot\phpinfo.php on line 2 Fatal error: require() [function.require http://localhost/function.require ]: Failed opening required 'DB.php' (include_path='.;C:\PHP\pear') in C:\wwwroot\phpinfo.php on line 2 7. Figure DB might not be installed by default, so follow the website's instructions by attempting to call it from the command line: pear install db = not recognized as internal or external command, bla bla bla c:\php\pear install db = see above c:\php\pear\pear install db = see above 8. get annoyed, go back to phpinfo file, remove erroneous line and look for references to PEAR, come up with the following (only) line: include_path~.;C:\PHP\pear~.; C:\PHP\pear 9. Go crazy, hit cute, fuzzy animals. I apolagize for the length of my e-mail, but I know that being thorough helps most of the time. Does anyone have any advice or ideas? ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Duplicate rows
Depends on how you determine if something's a duplicate or not. For example, if it's just one column that can be used, you can do something like this: select ItemName, count(ItemName) from ItemListTable group by ItemName having count(ItemName) 1 That'll show you if ItemName is repeated. Then you can go back through and just search for ItemName and remove the ones you don't want. You can do pretty much the same thing as above but CONCATenating multple columns if that's what you need to do to determine uniqueness. I know you're dealing with 15k rows still, so you probably want something a little more automated. Without more info though, it's hard to say exactly what can be done. Hope that helps a little bit. -TG = = = Original message = = = My dear beloved friends, I have a catalog of products that a product provider gave, sadly for me, in this CSV file there are many duplicated rows. I edited the file in my Linux system with the uniq -u command, and it worked somewhat fine, it eliminated some duplicated rows, originally the file had 24K rows, and now it has been reduced to 15k rows. Anyhow, there are still duplicated rows, and since this is a catalog, it should not have duplicated rows!!! Now the catalog has been has been loaded into the DB. How can I continue eliminating duplicated rows? As far as I remember the is a sentence in SQL to only show ONE row of duplicated rows, maybe if I do a select using this sentence and then put this new recordset in another table, it will work!! Any ideas? --- Miguel Guirao Aguilera Logistica R8 TELCEL Tel. (999) 960.7994 Este mensaje es exclusivamente para el uso de la persona o entidad a quien esta dirigido; contiene informacion estrictamente confidencial y legalmente protegida, cuya divulgacion es sancionada por la ley. Si el lector de este mensaje no es a quien esta dirigido, ni se trata del empleado o agente responsable de esta informacion, se le notifica por medio del presente, que su reproduccion y distribucion, esta estrictamente prohibida. Si Usted recibio este comunicado por error, favor de notificarlo inmediatamente al remitente y destruir el mensaje. Todas las opiniones contenidas en este mail son propias del autor del mensaje y no necesariamente coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna de sus empresas controladas, controladoras, afiliadas y subsidiarias. Este mensaje intencionalmente no contiene acentos. This message is for the sole use of the person or entity to whom it is being sent. Therefore, it contains strictly confidential and legally protected material whose disclosure is subject to penalty by law. If the person reading this message is not the one to whom it is being sent and/or is not an employee or the responsible agent for this information, this person is herein notified that any unauthorized dissemination, distribution or copying of the materials included in this facsimile is strictly prohibited. If you received this document by mistake please notify immediately to the subscriber and destroy the message. Any opinions contained in this e-mail are those of the author of the message and do not necessarily coincide with those of Radiomovil Dipsa, S.A. de C.V. or any of its control, controlled, affiliates and subsidiaries companies. No part of this message or attachments may be used or reproduced in any manner whatsoever. ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Duplicate rows
Haha.. oh yeah.. DISTINCT works too.. in this case you'd get a list of all totally 100% unique records. If you had an auto_increment column though, you'd want to exclude it from the list. -TG = = = Original message = = = SELECT DISTINCT * FROM `tablename` On Wednesday 01 March 2006 7:24 am, Miguel Guirao wrote: My dear beloved friends, I have a catalog of products that a product provider gave, sadly for me, in this CSV file there are many duplicated rows. I edited the file in my Linux system with the uniq -u command, and it worked somewhat fine, it eliminated some duplicated rows, originally the file had 24K rows, and now it has been reduced to 15k rows. Anyhow, there are still duplicated rows, and since this is a catalog, it should not have duplicated rows!!! Now the catalog has been has been loaded into the DB. How can I continue eliminating duplicated rows? As far as I remember the is a sentence in SQL to only show ONE row of duplicated rows, maybe if I do a select using this sentence and then put this new recordset in another table, it will work!! Any ideas? --- Miguel Guirao Aguilera Logistica R8 TELCEL Tel. (999) 960.7994 Este mensaje es exclusivamente para el uso de la persona o entidad a quien esta dirigido; contiene informacion estrictamente confidencial y legalmente protegida, cuya divulgacion es sancionada por la ley. Si el lector de este mensaje no es a quien esta dirigido, ni se trata del empleado o agente responsable de esta informacion, se le notifica por medio del presente, que su reproduccion y distribucion, esta estrictamente prohibida. Si Usted recibio este comunicado por error, favor de notificarlo inmediatamente al remitente y destruir el mensaje. Todas las opiniones contenidas en este mail son propias del autor del mensaje y no necesariamente coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna de sus empresas controladas, controladoras, afiliadas y subsidiarias. Este mensaje intencionalmente no contiene acentos. This message is for the sole use of the person or entity to whom it is being sent. Therefore, it contains strictly confidential and legally protected material whose disclosure is subject to penalty by law. If the person reading this message is not the one to whom it is being sent and/or is not an employee or the responsible agent for this information, this person is herein notified that any unauthorized dissemination, distribution or copying of the materials included in this facsimile is strictly prohibited. If you received this document by mistake please notify immediately to the subscriber and destroy the message. Any opinions contained in this e-mail are those of the author of the message and do not necessarily coincide with those of Radiomovil Dipsa, S.A. de C.V. or any of its control, controlled, affiliates and subsidiaries companies. No part of this message or attachments may be used or reproduced in any manner whatsoever. ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing money values in MySQL
Thanks Balazs and David.. I think that's exactly what I was looking for. I searched for hours (and I consider myself a fairly decent researcher) and was just getting frustrated..hah.. I think my problem was I was searching for money and float problems and such and not monetary. I think that's the keyword that would have done it for me. I appreciate the help guys! Best of luck to both of you (and anyone else I inadvertantly missed who may have responded). -TG = = = Original message = = = Me again, Sorry for the rtfm stuff, I googled around and found this: http://bugs.mysql.com/bug.php?id=10719 and another thing, maybe worth a try; MySQL stores decimal numbers as strings...did you try to input decimals as string? I mean not: UPDATE SomeTable SET AmtOwed = 10.74 but: UPDATE SomeTable SET AmtOwed = '10.74' Bye, Balazs ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Date Time 90 minutes ago
Bastien's example is probably the quickest and easiest. I just wanted to point out that you can use math within the mktime() function as well in case the relative date/time you need isn't right now. $month = 1; $day = 19; $year = 2006; $hour = 17; $minute = 08; $second = 05; echo date(Y-m-d H:i:s, mktime($hour, $minute - 90, $second, $month, $day, $year)); It will even adjust for leap years I believe. You can add/subtract/etc any of those items and it's smart enough to figure out what the correct resulting date/time would be. -TG = = = Original message = = = ?php echo date(Y-m-d H:i:s,strtotime(90 minutes ago)); ? bastien From: Ron Piggott (PHP) [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: PHP DB php-db@lists.php.net Subject: [PHP-DB] Date Time 90 minutes ago Date: Thu, 19 Jan 2006 16:57:33 -0500 Would someone be able to help me with the DATE command syntax to know what the date and time was 90 minutes ago? I am trying to assign these values into two variables: $date_90_minutes_ago $time_90_minutes_ago I am not sure how to handle midnight where if the time is 00:10:00 ninety minutes earlier is a day before. Thanks. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Storing money values in MySQL
Again.. your forgiveness. This is a MySQL question. If the MySQL mailing lists would include a [MySQL Help] tag in their subject lines, I'd use them. What I receive from them is difficult to distinguish from spam half the time so I gave up. We had a problem a few months ago and now I can't find my notes relating to it. The problem we had involved storing money data as either float or decimal and having the 'cents' round improperly. And it wasn't just a matter of 10.05 becoming 10.06, it was something along the lines of 10.05 becoming 10.12. This was when just a straight UPDATE was performed. I remember doing it manually, not even through PHP, using absolutely no math functions, just a totally straight: UPDATE SomeTable SET AmtOwed = 10.74 Someone made a recommendation of never use SOMETYPE for money.. you should use SOMEOTHERTYPE instead. I've spent the better part of the afternoon trying to find my notes, recreate the bug in our database, search online for the discussion I had then or other information about this. So far I'm coming up empty. We're retooling some of our database and trying to make it more efficient and accurate and this is on the list of things to doublecheck. If anyone has any comments, criticisms, information, etc... I'd love to hear them. We're running MySQL 4.1.11-standard. Thanks! -TG ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Refreshing text question!
Two ways I can think of to update part of a page without doing a full refresh: 1. Use an IFRAME so you're technically updating a page, but it's the page within the frame (not my favorite but works ok) 2. Or you can use asynchronous javascript (AJAX) to update just that one section of the page without doing a refresh. Here's Rasmus' great primer on AJAX, should be easy enough to follow: http://www.time.gov/timezone.cgi?Eastern/d/-5/java One note I'd like to make that doesn't seem to be mentioned in a lot of AJAX tutorials. If the page your AJAX is calling doesn't have the standard do not cache tags, you sometimes won't get the results you want.. especially with Internet Explorer (in my experience). Here's a block I copy/pasted from somewhere that seems to work fine (covers most of the bases and all): header(Expires: Mon, 26 Jul 1997 05:00:00 GMT); // Date in the past header(Last-Modified: . gmdate(D, d M Y H:i:s) . GMT); // always modified header(Cache-Control: no-store, no-cache, must-revalidate); // HTTP/1.1 header(Cache-Control: post-check=0, pre-check=0, false); // HTTP/1.1 header(Pragma: no-cache); // HTTP/1.0 good luck! -TG = = = Original message = = = Hi, No, no that is not what i am after, I know how to do that but it's not what I want. I wrote some code a year or so ago with words, they refreshed every 5 or so seconds WITHOUT REFRESHING THE PAGE. It was written in JavaScript as i can remember. But I can't find where I did it. I want to have the code or a similar one without the need to re-write it. The code does not require the page refreshing at all, just loops in a script and outputs. I might have to do some web surfing on it, pity I can't remeber where it is. If anyone could help that would be great. It's for a PHP site using MySQL. I am considering having the slogans in a database this time rather than just a text file. J Julien Bonastre [EMAIL PROTECTED] wrote: Yes I can help you there.. There is a technique you can use which is actually more browser friendly then the Javascript alternative you mentioned.. You can use the META tags in your page as such: will refresh page in 10 seconds CONTENT=10;url=http://www.operation-scifi.com; will reload page in 10 seconds and direct browser to one of my first highschool webpages.. For a quick reference I found this via the I'm Feeling Lucky of Google: http://webdesign.about.com/cs/metatags/a/aa080300a.htm Otherwise the good ole' W3C at www.w3c.org will have some great doco's on it too enjoy ;-) ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL date casting..
Unfortunately, no. The dates and times are stored as text. So here's what I get: 2006-01-10 07:00 PM 2006-01-10 08:00 PM 2006-01-10 09:00 AM 2006-01-10 09:00 PM (notice the AM out of order) For anyone interested, here's the big ugly version.. if anyone knows of a function that I can use instead of all this garbage, let me know: select ApptDate, ApptTime, CONCAT(ApptDate, ' ', IF(SUBSTRING(ApptTime, 7, 2) = 'PM', CONCAT(SUBSTRING(ApptTime, 1, 2) + 12, ':', SUBSTRING(ApptTime, 4, 2), ':00'), IF(SUBSTRING(ApptTime, 7, 2) = 'AM', CONCAT(SUBSTRING(ApptTime, 1, 2), ':', SUBSTRING(ApptTime, 4, 2), ':00'), '00:00:00'))) AS ProperDateTime from SomeTable I'm not worried about blank or -00-00 type dates in this case so don't really account for them. And again, this is meant to work on separate date and time fields in the following formats: ApptDate: -MM-DD (we at least did that part 'normal') ApptTime: HH:MM [AM|PM] Thanks for the response though Stephen... always worth going over the simple stuff first. :) -TG = = = Original message = = = Could you not just add: ORDER BY ApptDate, ApptTime To the end of your SQL - this will order the set by date then time. You can also add ASC or DESC for Ascending or Dscending as desired. N.B. the ORDER BY statement must be the last part of you query! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 17 January 2006 16:42 To: php-db@lists.php.net Subject: [PHP-DB] MySQL date casting.. Forgive me that this isn't really PHP related, but solely MySQL.. but the MySQL mailing lists drive me nuts and figured someone here would have a quick answer. I'm trying to sort by a date and time field(s) (two separate fields). It's a dumb system but until we do the next revision, it's going to stay the way it is (boss' orders) so bear with me. Example: ApptDate~ApptTime 2005-11-02~01:00 PM 2005-10-27~07:00 PM 2005-06-25~10:30 AM -00-00~N/A -00-00~N/A -00-00~N/A -00-00~06:30 PM See? Dumb.. hah.. So I thought I could do something like this: select ApptDate, ApptTime, DATE_FORMAT(CONCAT(ApptDate, ' ', ApptTime), '%Y-%m-%d %H:%i:%s') from Table But it doesn't like 06:00 PM.. returns null on the items that have a valid date and time because the time format isn't what it wants. If I try it with a 06:00:00 PM time, it makes it 6am. Using STR_TO_DATE() does exactly the same thing. You'd think STR_TO_DATE() would behave more like PHP's strtotime() but apparently not. I can code a big complicated conditional SQL statement, but I'm hoping there's a way to convert at least the valid date/time pairs into a happily ORDER BY'd column. I can handle the -00-00 and N/A entries with exceptions if I need to. And I would really like to do this without pre-loading the data into PHP and sorting it with PHP's sort functions. Any MySQL gurus who can show me what I'm missing here? Thanks in advance! -TG ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL date casting..
Ahh! Thank you Philip! That's what I was looking for! I see what I did wrong
now.
I was using the date format strings wrong. I was using it like I'd use it for
DATE_FORMAT() instead of as an input filter.
This is what I was trying to do:
select STR_TO_DATE('2003-11-05 06:00 PM', '%Y-%m-%d %H:%i:%s')
duh... hah. Thanks! I knew it was something simple.
-TG
--
What's wrong with [using] this?
mysql select str_to_date('2005-10-27 07:00 PM', '%Y-%m-%d %l:%i %p');
+-+
| str_to_date('2005-10-27 07:00 PM', '%Y-%m-%d %l:%i %p') |
+-+
| 2005-10-27 19:00:00 |
+-+
1 row in set (0.04 sec)
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Formatting a form box
What? Nobody said This has nothing to do with PHP and databases? Sheesh...
someone must be sleeping. :) Ok, so it sorta does relate..
But yeah... no2br() will do it for you. Textarea input types DO send a newline
and/or carriage return (didn't test and might be system specific), so if you
just take your form $_POST data and use nl2br() before you store it in the
database (or probably after... should be able to store a newline/carriage
return in the database) it should produce the results you desire.
test.php:
form action=test.php method=post
textarea name=testarea
/textarea
input type=submit
/form
?php
if (isset($_POST['testarea'])) {
echo nl2br($_POST['testarea']);
}
?
Look at the source code after you submit if you have any doubts/questions.
-TG
= = = Original message = = =
use the nl2br() function to convert the newlines to br
bastien
From: Alex Major [EMAIL PROTECTED]
To: php-db@lists.php.net
Subject: [PHP-DB] Formatting a form box
Date: Thu, 12 Jan 2006 16:33:01 +
Hi there,
On one of my forms, there is a section where users can put images etc..and
then it is displayed on my php pages. My question is how do I make it so
that the html formatting is automatically done. For example when someone is
typing something, and wants to start a new paragraph although they press
return when typing when the data is displayed from the MySQL database on a
website it's just continuous text. For it to start a new paragraph they
have
to put p in when they are typing in this box. They do not know all the
html formatting codes, and so I need someway of making so that it is easy
for them to have formatted text without doing all the html.
Also things like changing colours, bold, italic and things would be good.
Something like on forums, where when you enter a new post you have all the
formatting options.
Hope this makes sense,
Regards,
Alex.
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Date Formatting Question
You got the right idea, but you're making it more complicated than it needs to be. your $sDate after using explode() is going to contain an array. strtotime doesn't take an array, it takes a string. $monthName = date(F, strtotime($row_events['Sdate'])); $monthNumber = date(m, strtotime($row_events['Sdate'])); // or n if you want 1 instead of 01 for January echo select name='sMonth'\n; for ($i = 1; $i = 12; $i++) // using date() below to get month name, day and year irrelevant $selectMonthText = date(F, mktime(0, 0, 0, $i, 1, 2000)); if ($i == $monthNumber) $selected = SELECTED; else $selected = ; echo option value='$i'$selected$selectMonthText/option\n; echo /select\n; -TG = = = Original message = = = I am trying to format the month portion of a date that I am trying to pull from MySQL to be placed into a drop down menu to modify the date. I have tried several ways and none seem to be working. I am pulling the date out of MySQL with: $sDate = explode(-, $row_events['Sdate']); And then attempting to insert each portion of the array into a drop down menu with: echo select name=Smonth; echo option selected$sDate[1]/option; which is where I am running into the problem. I pull out the month as 2 digit numeric 01, 02, 03 etc., but I want it displayed as January, February, March, etc., I have tried the following with no success: Date(F,strtotime($sDate)); Strftime(%B:,$sDate); Date(F,$sDate); I would use MySQL to format the date, but I have three date fields to modify and it would be easier to do it in PHP Any pointers would be appreciated. Mark Bomgardner Technology Specialist KLETC ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Minor Change
This isn't really a MySQL error (sorta), it's a PHP error. You probably forgot
to update a variable name when you updated everything else.
Here's an example sequence for querying using PHP/MySQL:
$TestQY = SELECT * from SomeTable;
$TestRS = mysql_query($TestQY) or die(Error executing query);
while ($TestRW = mysql_fetch_assoc($TestRS)) {
$somearr[] = $TestRW; // do something with data
}
Since you're getting a Not a valid MySQL result resouce error with the
mysql_fetch_assoc() function, I'd search for all your mysql_fetch_assoc()
statements and double check their $TestRS. That error is saying that your
$TestRS isn't a valid MySQL result set. That could mean that $TestRS isn't
defined (maybe you're still using $TestOldRS and forgot to change a variable
name) or possibly that $TestQY is empty or bad somehow so mysql_query() isn't
generating a proper MySQL result set (try echoing out your $TestQY to see what
it is.. then try executing it manually on the MySQL server and see if you get
an error).
You can try the or die() syntax I use above to see if mysql_query() is
bombing out so you'll get notice before it even gets to the mysql_fetch_assoc().
Lastly.. someone recommended echoing out mysql_error(). Your response makes it
sound like you think that this fixes your problem. It's not going to fix
anything, just possibly give you some information about what failed. If you
get a MySQL error message from mysql_error(), please post it. It might help us
determine what the problem is.
It may not contain anything (under certain failing circumstances) so I'd step
through the things I've listed above. They may shed some light on where the
error is and then we can figure out how to fix it. Probably a typo in a
query, variable name or table name I'm guessing.
Let us know if you find anything else out.
-TG
= = = Original message = = =
After adding echo mysql_error(); I get the same result. I tried changing
the query to include 109fh7 (a table which doesn't exist) and got the same
result as with 109fh6. Changing to 109fh5 does pull up that table. The
line to which the error message refers is while ($row = mysql_fetch_assoc
($data_set)) That is what always come up when there is an error in the
query.
**
On Mon, 12 Dec 2005 14:13:10 -0500, Micah Stevens
[EMAIL PROTECTED] wrote:
You're getting an error, after the query, put:
echo mysql_error();
to find out what's happening.
On Monday 12 December 2005 11:05 am, [EMAIL PROTECTED] wrote:
I made tiny changes to my php file and sql table and the table won't
come
up. I updated the table name (and php file name) from 109fh5 to 109fh6.
In the table, I changed 6 cells, leaving a couple blank. Then I changed
only the digit 5 to make it a 6 (109fh6) in the following:
$get_data_query = select rep, party, state, cd, minority, afr_am,
asian,
am_indian, hispanic, med_hsehold_income, poverty from 109fh6 order by
$sort_field $sort_order;
Now I get Warning: mysql_fetch_assoc(): supplied argument is not a
valid
MySQL result resource in etc.
I've done this many times without a problem (this is the 6th time in
this
sequence). What could be wrong after such a minor change?
Ken
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Need a Help!
Couple of things you can do:
1. Drop the song ID and only get the artist information SELECT
distinct(Artist) from songtable. It doesn't look like your SELECT statement
needs a song, but you include the song ID as $id anyway. Any reason for that or
can you drop it so you only get artist?
2. Pre-parse the results of your current query so you only get one artist
and/or compile a list of song ID's while you're at it. Instead of doing your
option in the database query result WHILE statement, do it outside:
while (results) {
$artistinfo[$artist][] = $id;
}
Then:
echo select name='blah';
foreach ($artistinfo as $artist = $songsarr) {
echo optgroup label='$artist';
foreach ($songsarr as $songid) {
echo option value='$id'$id or whatever/option;
}
echo /optgroup;
}
echo /select;
3. Use a different DB structure. I prefer this structure myself:
Table ARTISTS:
ArtistID
ArtistName
OtherArtistInfo
Table SONGS:
SongID
ArtistID
OtherSongInfo
Or, instead of having ArtistID, if more than one artist may be linked to a
song, you can do:
Table xrefArtistsSongs:
ArtistID
SongID
Not sure why you'd want to do that for a song unless you're counting covers of
songs and want all artists who've covered it to point to the same SongID data.
This way you can:
SELECT * from ARTISTS
for your ARTISTS select box
Then once an artist is selected, do a:
SELECT * from SONGS where ARTISTID = $artistid
Just some ideas.
Forgive the pseudo-code and mixed capitalizations.. I think you get the idea
I'm trying to convey.
-TG
= = = Original message = = =
I have table and insert data using song_id, that means one artist can have
many song_ids. my question is how can I query distinct artist with his/her
song_id while I will not get duplicate data like duplicate artist?
my code is like this
$content .=form id=\form1\ method=\post\ action=\\
select name=\Quick\ onchange=\MM_jumpMenu('parent',this,0)\
option value=\#\Select Artist/option;
$result= $db-sql_query(SELECT distinct(artist), id FROM
.$prefix._lyrics order by artist asc);
if ($db-sql_numrows($result))
while($row = $db-sql_fetchrow($result))
extract($row);
$content .=option value='modules.php
?name=$module_nameamp;file=artistamp;c_id=$id'$artist/option;
$content .=/select
/form;
that code is fine except it gives me duplicate artist, so I want get rid off
that duplicate. any help
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing an array on a table?
Sorry, didn't catch this thread from the beginning, but did anyone recommend trying the serialize() and unserialize() commands? They'll convert the array to a block of text that can be stored, retrieved and unserialized. My gut instinct is that if you're trying to store any array in a database, you may not have thought through your design very well. BUT.. I also know that there are cases where you might want to (I've actually done it before... being lazy in that case..hah) so dont take that as criticism, just wondering if there's a more right way to do it. If that's what you need to do though, definitely check out serialize (unless someone knows something I don't). Serialize() should do essentially what's being proposed below, just without having to figure out what string may not be in your array. good luck! -TG = = = Original message = = = if you just have a simple array with automatic numeric keys and text you could just implode the data to a string with a separator not found in your data: $dataArray = array(hello, goodbye, etc.); $storable = implode(, $dataArray); // $storable becomes hellogoodbyeetc. //then, use explode to get the original array back again $dataArray = explode(, $storable); you could use a similar technique if you want to put the keys in as well, albeit slightly more complicated (e.g. use to separate each element in the array and || to separate each key from its value). Just find a divider you know your data will not contain, such as a pipe: |. This has worked well for me. Jordan On Sep 1, 2005, at 8:55 AM, Miguel Guirao wrote: I want to store an array into a field on a MySQL table, Is it posible to save it? Maybe into a string field? ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing an array on a table?
Ahh..thanks Jordan.. sorry I missed that one and thanks for the info. I assumed serialize was just magic and worked properly. hah. Now I know. I always thought it was sloppy to use anyway, but in a pinch, it's nice to know there's an option like that. Imploding does sound better though. -TG = = = Original message = = = Yes, this has been mentioned in this thread. But with serialize/ unserialize, you can run into other problems that may be more confusing/difficult to troubleshoot. e.g.: http://www.php.net/serialize As you can see, the original array : $arr[20041001103319] = test after serialize/unserialize is: $arr[683700183] = test yepp, and i can explain it. the internal compiler of php does not hit anys rule wich foces him to make that number a string during serialisation. since it becomes an integer and php supports 32bit interger not arbitary bitwidth this is what happens: 20041001103319 equals hexadecimal: 0x123A28C06FD7h if you cut away the frontpart cutting down to 32bis, you get: 0x28C06FD7h wich equals 683700183. For simple arrays, I prefer storing everything as a simple imploded string. YMMV. Jordan On Sep 1, 2005, at 10:18 AM, [EMAIL PROTECTED] tg- [EMAIL PROTECTED] wrote: Sorry, didn't catch this thread from the beginning, but did anyone recommend trying the serialize() and unserialize() commands? They'll convert the array to a block of text that can be stored, retrieved and unserialized. My gut instinct is that if you're trying to store any array in a database, you may not have thought through your design very well. BUT.. I also know that there are cases where you might want to (I've actually done it before... being lazy in that case..hah) so dont take that as criticism, just wondering if there's a more right way to do it. If that's what you need to do though, definitely check out serialize (unless someone knows something I don't). Serialize() should do essentially what's being proposed below, just without having to figure out what string may not be in your array. good luck! -TG = = = Original message = = = if you just have a simple array with automatic numeric keys and text you could just implode the data to a string with a separator not found in your data: $dataArray = array(hello, goodbye, etc.); $storable = implode(, $dataArray); // $storable becomes hellogoodbyeetc. //then, use explode to get the original array back again $dataArray = explode(, $storable); you could use a similar technique if you want to put the keys in as well, albeit slightly more complicated (e.g. use to separate each element in the array and || to separate each key from its value). Just find a divider you know your data will not contain, such as a pipe: |. This has worked well for me. Jordan On Sep 1, 2005, at 8:55 AM, Miguel Guirao wrote: I want to store an array into a field on a MySQL table, Is it posible to save it? Maybe into a string field? ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection attack
I'm pretty amateur at this too, but have done a little reading on the subject.
Here's some nuggets to ponder while the real experts write their responses: :)
1. Magic quotes + mysql_escape_string = double escaped stuff. I think the
general opinion is the magic quotes is evil, but I'm sure some people like it.
I prefer to use mysql_escape_string() since it escapes things more specific to
MySQL than magic quotes does. Using mysql_escape_string should be good enough
by itself.
2. Check data type. If an item is supposed to be an integer, use intval()
before inserting into the database.
3. What your SQL statements for variables that can turn your statement into a
WHERE 1 = 1 situation that will always return TRUE.
Here's something I've been playing with.. a generic function to sanitize data
before inserting into the database. You pass it the data and the type of data
and it'll clean it up. Nice thing about this is I designed it so if you say
type = phone and you process it the same as type = numeric.. then later you
decide you want to process phone and numeric types separately, you only
have to check this function, not all your lines of code.
If someone has better ways of doing this, I'm all for hearing it. Please opine
or criticize what I've posted above too. I want to learn as well.
-TG
Code:
?php
/**
*~DBSanitizeData() prepares data for inserting/updating into or selecting from
* MySQL by making sure that string data is properly escaped so as not to allow
* 'SQL injection' type security issues from happening. No direct $_POST or
$_GET
* data should ever be used in a SQL string.
*
* Returns sanitized copy of data sent to it.
*
* Current sanitization only performs a mysql_escape_string() function but could
do
* more later.
*
* Example: $result = mysql_query('INSERT INTO TableName (SomeColumn) VALUES ('
. DBSanitizeData($_POST['somevar']) . ')');
*
* pre
* Modification Log:
* --
* Created: ~~Trevor Gryffyn - 03/28/2005
*
* /pre
*
* @author Trevor Gryffyn [EMAIL PROTECTED]
* @category Database Functions
*
*/
function DBSanitizeData($dbdata, $datatype = alpha) {
switch ($datatype) {
case binary:
case truefalse:
$trues = array(YES, Y, 1, ON, TRUE, T);
$falses = array(NO, N, 0, OFF, FALSE, F);
if (in_array(trim(strtoupper($dbdata)), $trues)) {
$dbdata = Y;
} else {
$dbdata = N;
}
break;
case phone:
case numeric:
case ssn:
$dbdata = preg_replace ('/[^\d]+/s', '', $dbdata);
break;
case float:
case money:
case percent:
// TODO: Should this be handled with floatval() or something else?
// Yes.. it probably should. Maybe this is better.
if (strstr($dbdata, .) AND trim($dbdata) ) {
#$dbdata = (preg_replace ('/[^\d]+/s', '', $dbdata) / 100) . .00;
$dbdata = floatval(preg_replace ('/[^\d]+/s', '', $dbdata) / 100);
} else {
#$dbdata = preg_replace ('/[^\d]+/s', '', $dbdata) . .00;
$dbdata = floatval(preg_replace ('/[^\d]+/s', '', $dbdata));
}
break;
case name:
case address:
$dbdata = ucwords($dbdata);
break;
case state:
$dbdata = strtoupper($dbdata);
break;
case date:
$dbdata = date(Y-m-d, strtotime($dbdata));
if ($dbdata == 1969-12-31) $dbdata = ;
break;
case alpha:
default:
// Nothing special, just jump down to the trim/escape
break;
}
return trim(mysql_escape_string($dbdata));
}
?
= = = Original message = = =
Greetings all:
Using PHP 4.3.xx and MySQL 4.1 (and 3.xxx sometimes).
I've got a ton of forms that use the $_POST variable to send information into
the database, and I'm worried about injection attacks.
My server has magic_quotes enabled, which I thought would handle most things,
but am wondering now if I need to use mysql_escape_string on everything, which
would mean, of course, a lot of find-and-replace and rewriting.
Also, REGISTER_GLOBALS is turned off, and errors are not shown to the user when
the site is live.
Any suggestions on how to tighten up the form security, or does magic_quotes
help enough?
For what it's worth, I've tried to enter things like pw='' and other
simulated attackes using the $_GET method, but haven't been able to crack the
site. But I'm a noob at that kind of thing, so I try not to get too carried
away with myself.
Thanks,
V
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection attack
Haha.. what the hell? Ok, I know this is an older copy of the script I wrote
because I know I took out the All this does is escape the data comment and I
KNOW I saw the thing about mysql_escape_string() being deprecated... don't
know why it's still in there. Hah
Thanks for pointing that out. Now off to find my newer version and make sure I
chaned it there too.
-TG
= = = Original message = = =
no !!!
mysql_real_escape_string()
anyhow.. good luck with your security endeavors!
On 8/25/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
I'm pretty amateur at this too, but have done a little reading on the
subject. Here's some nuggets to ponder while the real experts write their
responses: :)
1. Magic quotes + mysql_escape_string = double escaped stuff. I think the
general opinion is the magic quotes is evil, but I'm sure some people like
it. I prefer to use mysql_escape_string() since it escapes things more
specific to MySQL than magic quotes does. Using mysql_escape_string should
be good enough by itself.
2. Check data type. If an item is supposed to be an integer, use intval()
before inserting into the database.
3. What your SQL statements for variables that can turn your statement into a
WHERE 1 = 1 situation that will always return TRUE.
Here's something I've been playing with.. a generic function to sanitize data
before inserting into the database. You pass it the data and the type of
data and it'll clean it up. Nice thing about this is I designed it so if you
say type = phone and you process it the same as type = numeric.. then
later you decide you want to process phone and numeric types separately,
you only have to check this function, not all your lines of code.
If someone has better ways of doing this, I'm all for hearing it. Please
opine or criticize what I've posted above too. I want to learn as well.
-TG
Code:
?php
/**
*~DBSanitizeData() prepares data for inserting/updating into or selecting from
* MySQL by making sure that string data is properly escaped so as not to allow
* 'SQL injection' type security issues from happening. No direct $_POST or
$_GET
* data should ever be used in a SQL string.
*
* Returns sanitized copy of data sent to it.
*
* Current sanitization only performs a mysql_escape_string() function but
could do
* more later.
*
* Example: $result = mysql_query('INSERT INTO TableName (SomeColumn) VALUES
(' . DBSanitizeData($_POST['somevar']) . ')');
*
* pre
* Modification Log:
* --
* Created: ~~Trevor Gryffyn - 03/28/2005
*
* /pre
*
* @author Trevor Gryffyn [EMAIL PROTECTED]
* @category Database Functions
*
*/
function DBSanitizeData($dbdata, $datatype = alpha)
switch ($datatype)
case binary:
case truefalse:
$trues = array(YES, Y, 1, ON, TRUE, T);
$falses = array(NO, N, 0, OFF, FALSE, F);
if (in_array(trim(strtoupper($dbdata)), $trues))
$dbdata = Y;
else
$dbdata = N;
break;
case phone:
case numeric:
case ssn:
$dbdata = preg_replace ('/[^\d]+/s', '', $dbdata);
break;
case float:
case money:
case percent:
// TODO: Should this be handled with floatval() or something else?
// Yes.. it probably should. Maybe this is better.
if (strstr($dbdata, .) AND trim($dbdata) )
#$dbdata = (preg_replace ('/[^\d]+/s', '', $dbdata) / 100) . .00;
$dbdata = floatval(preg_replace ('/[^\d]+/s', '', $dbdata) / 100);
else
#$dbdata = preg_replace ('/[^\d]+/s', '', $dbdata) . .00;
$dbdata = floatval(preg_replace ('/[^\d]+/s', '', $dbdata));
break;
case name:
case address:
$dbdata = ucwords($dbdata);
break;
case state:
$dbdata = strtoupper($dbdata);
break;
case date:
$dbdata = date(Y-m-d, strtotime($dbdata));
if ($dbdata == 1969-12-31) $dbdata = ;
break;
case alpha:
default:
// Nothing special, just jump down to the trim/escape
break;
return trim(mysql_escape_string($dbdata));
?
= = = Original message = = =
Greetings all:
Using PHP 4.3.xx and MySQL 4.1 (and 3.xxx sometimes).
I've got a ton of forms that use the $_POST variable to send information into
the database, and I'm worried about injection attacks.
My server has magic_quotes enabled, which I thought would handle most things,
but am wondering now if I need to use mysql_escape_string on everything,
which would mean, of course, a lot of find-and-replace and rewriting.
Also, REGISTER_GLOBALS is turned off, and errors are not shown to the user
when the site is live.
Any suggestions on how to tighten up the form security, or does magic_quotes
help enough?
For what it's worth, I've tried to enter things like pw='' and
Re: [PHP-DB] Case sensitive
One trick is to force the case in your comparison: $ucstringinput = strtoupper($stringinput); $qry = select * from sometable where upper(address) like '%$ucstringinput%' Didn't think LIKE was case sensitive, but regardless... forcing upper or lowercase in your comparison doesn't affect output but will make your case sensitivity issue moot. HTH -TG = = = Original message = = = Hi there everyone, I have a little problem, I have a search where people can search the address of a property BUT the search is case sensitive, I don~t WANT it to be. I~m using MySQL and PHP and I generally use something like WHERE address LIKE ~%$stringinput%~ which works with the numbers ONLY, but when I add the address if I don~t put a capital infront of each part of the address it won~t show up. Any ideas how I can make it case INSENSITIVE? (Think that~s the correct phrase). Any help would really be appreciated. Thanks everyone Regards Chris Payne -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.15/80 - Release Date: 8/23/2005 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Connecting to SQL Server
If I remember right, PHP's built-in database functions don't allow for a DSNLess connection. Meaning you have to set up an ODBC source on the machine PHP is running on (or do a remote ODBC connection) and connect to a DSN (a named datasource manually set up). BUT.. if you know the address of the server and all the login information, you can do a DSNLess connection. At my last job I used PHP on an IIS server to connect to a couple SQL Servers and an Oracle server using ADODB for PHP. I posted about it ages ago.. here's a link to an archived message where I posted my DSN config file. Down here it says Cargo System databases you'll find examples that might help. Sorry to be vague, but hopefully it's a start for you. Once you get it configured, it should work fairly fluidly. -TG = = = Original message = = = I have been asked by my client to use SQL Server for my php application (uh!). The name of the server is 2k-server, but I am not sure where to create the ODBC connection. Some of the docs state that all I need is ?php $connection_string = 'DRIVER=SQL Server;SERVER=2K-SERVER;DATABASE=Php_Test'; $user = 'sa'; $pass = 'MyPassword'; $connection = odbc_connect( $connection_string, $user, $pass ); ? However that gives me an error: Warning: SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQLConnect in /home/httpd/test/odbc/php/connect.php on line 8 Any suggestions on the best way to connect? Todd ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Integrated Authentication on IIS 6.0
Maybe I'm not understanding the situation properly, because I can't see why you would have had it working under IIS5 if your configuration is how I think it is. Integrated authentication basically allows a workstation that's logged into a domain to automatically pass it's credentials from the workstation to the domain. Using this, you can get the currently logged in userid via server variables from IIS into PHP. But you never get the user's password or are able to get anything into PHP that will automatically connect you to a database or other trusted data source. If you connect to a web server and PHP on that server creates a connection to a database, then PHP needs to send login credentials (either hardcoded, pulled from another database based on the user's ID or something, or provided by the user via a web form). I don't believe you can have PHP create a connection based on integrated authentication because PHP is sort of outside the whole windows security scheme. You could configure the database to accept a connection from the PHP server's IP address and nowhere else and hardcode a password into PHP, or do other things like that. Again, maybe I mis-assuming what your configuration is. Mind giving us more detail on the process you're trying to fix? Users connects to web server, web server makes ODBC connection (by what means?), etc.. -TG = = = Original message = = = Hi, I have an intranet application that I wrote in PHP that has worked great for a long time. It uses integrated authentication in IIS. I'm trying to migrate to IIS 6 and things are mostly working. The problem I have is that my ODBC_CONNECT calls are not resulting in: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.. What it seems to tell me is it is correctly trying to make an integrated auth connection to the database, but for some reason the current user's credentials aren't being passed? Does anyone have insight into this? Thanks! Ryan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Integrated Authentication on IIS 6.0
Ahh.. ok.. I was mostly doing DSNless connections. You might want to look into the setup for your ODBC source called Datasource in your example below. That might be what's actually passing the authentication, not PHP itself. PHP makes a call to the local ODBC source which in turn actually handles the connection. Don't know if that helps, but that's the next place I'd check, unless someone else has better insight. -TG = = = Original message = = = Thanks for the response! Yes, believe it or not it does work, or at least did on IIS 5. When I turn on Integrated Security on the web server it causes PHP to run as the user logged in instead of the Anonymous user. Then calling odbc_connect with a blank username/password combination in cooperation with a system DSN configured to use Windows Authentication caused the connection to be made via the individual user's Windows credentials. This behavior is eluded to in a note on the php site from flo : If you don't want to specify your login credentials on your web server, you can leave the login fields blank to use the integrated windows security like here: odbc_connect(DSN=DataSource,,); Make sure you have switched your system dsn to integrated security, too ! (works on windows machines only, of course) My intranet application relies on this ability. My working production server is IIS 5 on Advanced Server 2000 with PHP 5.04. My new server is IIS 6 on Windows 2003 Server with PHP 5.04. Both are operating in ISAPI mode. $AUTH_USER does report correctly the authenticated user on both systems, but a call to odbc_connect on the new system gives this error: Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection., SQL state 28000 in SQLConnect in testOdbc.php on line 3 For some reason odbc_connect on IIS 6.0 is not acting the same as it is on IIS 5. I'm pretty sure it's a configuration problem, I just can't find it. :-\ Ryan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, May 23, 2005 12:38 PM To: php-db@lists.php.net Cc: Ryan Jameson (USA) Subject: Re: [PHP-DB] Integrated Authentication on IIS 6.0 Maybe I'm not understanding the situation properly, because I can't see why you would have had it working under IIS5 if your configuration is how I think it is. Integrated authentication basically allows a workstation that's logged into a domain to automatically pass it's credentials from the workstation to the domain. Using this, you can get the currently logged in userid via server variables from IIS into PHP. But you never get the user's password or are able to get anything into PHP that will automatically connect you to a database or other trusted data source. If you connect to a web server and PHP on that server creates a connection to a database, then PHP needs to send login credentials (either hardcoded, pulled from another database based on the user's ID or something, or provided by the user via a web form). I don't believe you can have PHP create a connection based on integrated authentication because PHP is sort of outside the whole windows security scheme. You could configure the database to accept a connection from the PHP server's IP address and nowhere else and hardcode a password into PHP, or do other things like that. Again, maybe I mis-assuming what your configuration is. Mind giving us more detail on the process you're trying to fix? Users connects to web server, web server makes ODBC connection (by what means?), etc.. -TG = = = Original message = = = Hi, I have an intranet application that I wrote in PHP that has worked great for a long time. It uses integrated authentication in IIS. I'm trying to migrate to IIS 6 and things are mostly working. The problem I have is that my ODBC_CONNECT calls are not resulting in: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.. What it seems to tell me is it is correctly trying to make an integrated auth connection to the database, but for some reason the current user's credentials aren't being passed? Does anyone have insight into this? Thanks! Ryan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] GROUP BY? Urgent help needed with selection list
Well, first of all.. your subject line mentions GROUP BY which is a database function for telling the query what to use when performing aggregate functions like sum, count, average, etc type functions. That's really not what you're looking at doing here it sounds like. Sounds like you just want similar items alphebetized together while still keeping a 'region' like 'SE Akron' to display. Probably the best thing you can do is have a City and have a Region. City would be the major city that the region is near, like Akron and the Region (or whatever you want to call it) would be the area of the city that the data represents. Then just do an ORDER BY on your City instead of Region. you never have to display the City if you don't want to. But without having a giant cross reference database (that can't ever be complete) that can do the lookup of SE Akron and know that's actually Akron and not some suburb of Chicago or something, then you're going to have to tell it yourself. I think this is the best solution for you. Best of luck! -TG = = = Original message = = = Hi there everyone, I~m using the following code to populate cities from a huge database: select name=fm_city[] id=fm_city[] multiple option value=0Show All/option ? $sqla = SELECT DISTINCT(Area) FROM MLS_Listings ORDER BY Area; $sql_resulta = mysql_query($sqla,$connection) or die(Couldn't execute query.); while ($row = mysql_fetch_array($sql_resulta)) $Area = $row[Area]; ? option value=?=$Area? ?=$Area? /option ? ; ? /select This works great, no problems BUT the client now needs is so the cities are grouped, but it~s not so simple. For example, say you have Akron, Akron Central etc ~. They need them so that ALL Akrons appear together, the problem is, that also includes some which are SE Akron etc ~. So, of course that appears further down the list under S. How can I group this way? I am pretty lost on this. Any help would REALLY be appreciated. Chris -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.2 - Release Date: 1/21/2005 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] to many addresses
Sounds like pre-loading the addresses into javascript or something is also going to be just as cumbersome and that's the only other method I can think of to have the addresses auto-complete realtime. Yahoo has a little helper app that works with Yahoo mail to auto-complete names from your email address book like Outlook or another non-web based email package would, but even that kind of solution would have a lot of client side overhead. Here are a couple of thoughts though: Thought 1: Have a 'lookup address' page where they enter their address and on the backend you strip out things like 'rd' 'road' 'ave' 'avenue', etc and just search for the base name of the street and maybe ignore city if a zip code is entered (because sometimes the city name might not match the city name of the zip code... in our area, someone might enter Baltimore, MD 1 when the post office name is reall Catonsville, MD 1. Zip code should always match, but the city may or may not. Take the results of the base street name + zip code (and/or city/state.. state should match the zip code unless it's a border town that's confused.. stupid human error) and display a list of addresses for the user to select, OR enter the new address (already pre-populated from what the user entered) Thought 2: Have them enter a few things like city and state and do a wizard style step by step to narrow down your 20,000 to a handful they can select from. I think I like the first one better. You might audit it on the back end though to make sure that people actually entered a unique address and didn't just go Yeah whatever and click Ok to the add new address box. Or maybe require them to enter it again by hand... something to keep them from being too lazy and always entering a new address. Good luck Matt! -TG = = = Original message = = = We have about 20,000 addresses in our database. If my users are filling out a form that requires an address already in our database I have to make sure they enter it in a format already in the db (such as West Road not West Rd.). I have to also allow for new addresses. Obviously having a dropdown of 20,000 entries and a second field called Other Address Not Here will not work. It would also be nice if the field auto fills with suggested addresses from the database. Does anyone have advice on how I should do this? I have been scratching my head over this problem all morning. -Matthew ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Comparing Two Values
To be honest, I havn't had much use for it myself but I've done a lot of 'hack' projects that didn't need to be this specific. But as I understand, you might want to use the triple-equal sign to determine if they're exactly the same. Are you comparing... 2 with... 0002 (integer versus a string?) If so, the === should work I believe. It should compare variable types as well as values so it shouldn't convert 0002 to just 2 before doing the evaluation. Good luck! -TG = = = Original message = = = Hi List, I was comparing two values, a current value with a new value to build the UPDATE instruction and I faced this: Current value: 2 == new value: 0002 So, PHP is telling me that the current is equal to the new. Is this possible? Any idea to avoid this problem? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
