[PHP-DB] Assistance on Query
Hi People, I would like some assistance on the following scenario. I have to pull out all records for a particular userid (easy enough) and then only show those entries where the follwing occurs. These records from the table will contain either an entry in the services_type field or the non_services_type field. What I need to do is show only those where the number of consecutive records that contain an entry in the non_services_type field is greater than or equal to 3 so example:- record 1 contains an entry in non_services_type record 2 contains an entry in services_type record 3 contains an entry in non_services_type record 4 contains an entry in non_services_type record 5 contains an entry in non_services_type record 6 contains an entry in services_type so I would need to display records 3,4,5 only Can anyone assist me with this? Cheers, Shannon -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Assistance on Query
Hi Jochem, My apologies, when I typed consecutive, I meant by date. Basically the query is for a report. The client wants to be able to see when there have been 3 or more entries of non_services_type between services_type entries, and then display these entries. I am using MySQL and would Order the entries by date. I hope that clears things up a little. - Shannon -Original Message- From: Jochem Maas [mailto:[EMAIL PROTECTED] Sent: Monday, January 17, 2005 1:37 AM To: Shannon Doyle Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Assistance on Query Shannon Doyle wrote: Hi People, I would like some assistance on the following scenario. I have to pull out all records for a particular userid (easy enough) and then only show those entries where the follwing occurs. These records from the table will contain either an entry in the services_type field or the non_services_type field. What I need to do is show only those where the number of consecutive records that contain an entry in the non_services_type field is greater than or equal to 3 so example:- record 1 contains an entry in non_services_type record 2 contains an entry in services_type record 3 contains an entry in non_services_type record 4 contains an entry in non_services_type record 5 contains an entry in non_services_type record 6 contains an entry in services_type so I would need to display records 3,4,5 only Can anyone assist me with this? Could you explain what the logic behind doing this is. i.e. Why? (it might help to understand the problem, and possibly give a solution that does not rely on 'consecutiveness') I'm guessing you are using MySQL which is a relational DB, that means the order of records stored is essentially abstracted from the DB user, for instance using an ORDER BY clause will change the order of the result and therefore the 'consectiveness' of the values. It looks as if you will need to post-process the result set in PHP to get what you want, alternatively your DB may support stored procedures which could be an alternative (but harder to do than to do it in PHP) i.e. the stored procedure works out the correct rows and then outputs the result when you something like: SELECT * FROM getConsecServiceRows( 3 ) rgds, Jochem Cheers, Shannon -- 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
RE: [PHP-DB] Dates and Count
Hi Pablo, Its exactly what I wanted. Thanks. I now have an additional question. I need to show results that appear between two dates as specified by the site visitor. At the moment I am using the following SQL: WHERE NextContact = '$today' NextContact = '$week'; Second I need the same scenario, however I want to search from a variety of different fields, show those that are between the dates and are equal to one other criteria. Any help on these two? Cheers, Shannon -Original Message- From: Pablo M. Rivas [mailto:[EMAIL PROTECTED] Sent: Monday, 28 June 2004 9:21 AM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Dates and Count Hello Shannon, SD First, I have two dates stored in a MySQL DB that I want to do a compare on SD and then only display via a web page those records that have a 5 or greater SD day difference in them. What would be the best way to achieve this. Depends on your mysql version... select * from mytable where date1 + INTERVAL 5 DAY=date2 or date2 + INTERVAL 5 DAY =date1; select * from mytable where to_days(date1)-todays(date2)5 or to_days(date2)-todays(date1)5 from the mysql manual: For other dates before 1582, results from this function are undefined if your mysql 4.1.1: select * from mytable where DATEDIFF(date1,date2)5 or DATEDIFF(date1,date2)-5 but take a look to optimization.. the first script took 0.0234 secs to return 256 row of 3096 rows the second took 0.0413 secs to return the same couldn't test the third (I have a mysql 4.0.18-standard-log) SD Second, I want to be able to return a list of 'clients' from the MySQL DB SD that have a specific number of a particular type of entry associated with SD them. I am assuming that the 'count' would be used, but how would I first SD display this count, and second only return those that have the number of SD entries that I am looking for. Select count(operation_id) as howmany, client_id, client_name from clients left join operations on operations.client_id = clients.client_id group by clients.client_id having howmany=5 Then... mysql_num_rows will give you how many clients have 5 operations, and each row will tell you: howmany (always = 5), id of the client, and name of the client. ¿is this what you where looking for?... -- Best regards, Pablo -- 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
[PHP-DB] Dates and Count
Hi People, I have two queries for the list:- First, I have two dates stored in a MySQL DB that I want to do a compare on and then only display via a web page those records that have a 5 or greater day difference in them. What would be the best way to achieve this. Second, I want to be able to return a list of 'clients' from the MySQL DB that have a specific number of a particular type of entry associated with them. I am assuming that the 'count' would be used, but how would I first display this count, and second only return those that have the number of entries that I am looking for. Thanks in advance Shannon -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Date Manipulation
Hi People, Need some assistance in the following scenario:- Inserting a date into the database that is entered into a form by the site visitor. - This is easy enough. However I now need to use the same date that has been entered by the site visitor add 35 days and then insert into another table. My question, how do I get the date entered into the form add 35days to it and then include that into the same sql query as the first one. Or do I have to use a second sql query? If the second query how would I get the date and add 35days?? Cheers, Shannon -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] populating select and searching to criteria
Hi People, I have a three part question here. Well actually its 3 separate questions. First. How would I populate a select menu from a mysql databse with the entries from a particular field, but only showing those that are unique, ie not showing more than 1 of the same value. Second. How would I then have one of those options selected depending on what it contained in another table? Third. I need to understand how the following would be achieved. In the database there is a text field that contains a number of 'keywords' I need to be able to search through those individual keywords and return results based on a % match from the original search. Ie if I search for 'secretary, typing, customer relations' I need to return all records from the database that has one or all of those words contained in the text field and display them as a % match of that search, ie if 1 match then 33% 2, 66% and so on. Any help on the above three would be fantastic. Cheers, Shannon -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] populating select and searching to criteria
Thanks Micah,
while ($d = mysql_fetch_assoc($return)) {
?option value=?=$d['value']??=$d['name']?/option - I get
a parse error on this line.
while ($d = mysql_fetch_assoc($return)) {
?option value=?=$d['value']??
if ($other_table_value == $d['value'])
echo selected;
??=$d['name']?/option
?
}
Ok cool, I am assuming that this will be OK once I work out the parse
error on the above option.
Third:
If you're using MySQL, check this out:
http://www.mysql.com/doc/en/Fulltext_Search.html
If you're using something else, it's more complex I think. Someone may
have an
elegant solution, but I would do something like:
// Get total number of keywords:
$numofkeywords = count(str_replace ( , , $keywords));
// Split up your search words:
$search = explode( , $keywords);
$total_matched = 0;
// loop through the array of search terms and get number of returns.
foreach ($search as $searchword) {
$total_matched += count(str_replace($searchword, $searchword,
$keywords));
}
// echo out the result in percent. (to one decimal place even!)
echo Percent Matched: .round(($total_matched/$numofkeywords)*100),
1). %;
This only partly resolves my problem, I am not looking for a word count,
I am looking to return those records that have one or more of the
keywords in them, and display a percentage result next to each record
that matches.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] REGEXP and Variables.
Hi People, Need a little assistance with the following MySQL query:- $query = SELECT employ_id,company FROM employers WHERE employ_id REGEXP '($employ_id)' OR company REGEXP '($name)' OR industry REGEXP '($industry)'; The above query gets the variables $employ_id, $name and $industry from a search box on a previous page. I can confirm that the variables are being parsed to the query. The problem is that the above query appears to match everything in the table, from any of the fields and thus just dumps out the entire contents of the table, instead of only selecting the ones that match the search criteria. Basically I have a variable passed to the query and it must return the values from the table that have the variable in its entirety somewhere in the fields value. In addition to this, once I have it selecting the correct values and returning them to me. What can I do to show a 'No Results' situation? Any help woul dbe gratly appreciated. Cheers, Shannon -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Mysql result resource error
Hi Jim,
Ahh yes of courseI have corrected this.
I am still getting the error on the first query...not sure why. And now
I am getting a whole bunch of empty lines being output to the screen.
Like there is supposed to be data in those lines, but nothing happening.
Plus I am getting repeats of the same data. Its like it is doing each
query three times or something.
Don't know why it is doing that.
- Shannon
-Original Message-
From: Jim Lucas [mailto:[EMAIL PROTECTED]]
Sent: Friday, 21 December 2001 03:52 AM
To: Shannon Doyle; [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Mysql result resource error
which one was giving you the problem? the first or second
mysql try. if it
was the second, try wrapping the $cattyname with single
quotes like this
Minor_Category = '$cattyname'. if the $cattyname var has anything but
numbers, the statement won't work.
Jim
- Original Message -
From: Shannon Doyle [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, December 19, 2001 4:19 PM
Subject: [PHP-DB] Mysql result resource error
Hi People,
I am getting a Not a valid Mysql result resource error with the
following code, can someone take a look at this for me and
see if there
is anything that stands out
Thanks,
Shannon
?
$cattyname = ;
$sql = select Minor_Category main where Page = 'wines' order by
Minor_Category;
$dbh = @mysql_connect($dbhost,$dbuser,$dbpass);
$results2 = mysql_db_query($db,$sql,$dbh);
for($j = 0; $j mysql_num_rows($results2); $j++) {
$array[$j] = mysql_fetch_array($results2);
}
mysql_close($dbh);
for ($h = 0; $h count($array); $h++) {
if ($array[$h][Minor_Category] != $cattyname) {
$cattyname = $array[$h][Minor_Category];
echo trtd colspan='4'a
name='.$cattyname.'/ab.$cattyname./b/td/td/tr;
$catname = ;
$sql = select Category,Code,Description,Pack,Unit,Price
from main where
Page = 'wines' Minor_Category = $cattyname order by Category;
$dbh = @mysql_connect($dbhost,$dbuser,$dbpass);
$results = mysql_db_query($db,$sql,$dbh);
for($i = 0; $i mysql_num_rows($results); $i++) {
$array[$i] = mysql_fetch_array($results);
}
mysql_close($dbh);
for ($i = 0; $i count($array); $i++) {
if ($array[$i][Category] != $catname) {
$catname = $array[$i][Category];
echo trtd colspan='4'a
name='.$catname.'/ab.$catname./b/tdtd
align='center'pbCarton Size/b/p/tdtd
align='center'pbUnit/b/p/tdtd
align='right'pbPrice/b/p/tdtda href='#top'img
src='images/arrow.gif' border='0'/a/td/tr;
}
echo trtd/tdtdpa
href=javascript:order_now(quot;.$array[$i][Code].quot;)img
src='images/order.gif' border='0'/a/tdtd/tdtd
align='left'p.$array[$i][Description]./p/tdtd
align='center'p.$array[$i][Pack]./p/tdtd
align='center'p.$array[$i][Unit]./p/tdtd
align='right'p$.$array[$i][Price]./p/td/tr\n;
}
}
}
?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail:
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail:
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Query Help
Hi people, I have a small problem with a select cause that I need to use. I basically need the following. Select * from table where page =3D=3D page_name The fields in the db are code,description,unit,price,category,minor_category Then the display needs to be something along the lines of Category Name Minor Category Code Description Unit Price Minor Category Code Description Unit Price Category Name Minor Category Code Description Unit Price The number of minor categories differ for each of the categories, as to = do the number of products in each minor_category. I could hard code in the Category Names, however, they are meant to be = dynamic as they will be changing on a monthly basis. Any help would be appreciated. Thanks Shannon ___ Shannon Doyle Web Design Consultant BIGBLUE Internet Pty Ltd 149 Hutt St, Adelaide SA 5000 Ph +61 8 8232 1444 Fax +61 8 8232 8577 http://www.bigblue.net.au -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] MySQL Query structure
Hi people, I was approached by my boss today to construct a page to draw out the data from a recent survey that was conducted via the web. The data is stored in a mysql database by fieldname and the data that the user entered. The problem lies in the way that he wants the page constructed. The page is split into two sections that in essence build the structure of the sql query. At the top of the page there are a number of checkboxes that will both have a variety of fieldnames associated with them. ie if the checkbox is checked then there are a number of fieldnames to be included into the query. These can be checked in any number of ways to include a different query each time. While I can work out a way to do this, the problem comes when I retrieve the results from the database. I set the query to draw out the required information from the database with no problems. However by using a mysql_fetch_row statement I hit a wall. How do I write the statement to reflect the query statement? ie if the query can be: select field1,field2,field3 from database where data = test select field4,field5,field6 from databse select field1, field4, field6 from database where data = test I think you can see the pattern here. Subsequently the displaying of the results will also be affected in the same way. There is a second area of the form that I can solve by using a different form tag, but the same problem will arise here. Any help on this would be greatly appreciated. Thankyou in advance. Shannon -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Varible insert
Hi People, It seems like all I am doing lately is going into areas of php that I haven't done before and get stuck. Anyway, here is another small request that I have. I have a series of pages that must insert into a mysql database the results of the entries. I know how to insert these details into a database, however there are over 200 entries across 20 or so pages. What I am in need of doing is the following: Is there a way of creating one insert statement that can be used by all pages to insert the data from the fields into the appropriate fields in the database? ie the sql insert statement is dynamic and will adapt. I have an idea that maybe the "get" method on the form will be of some help, but not sure. Any ideas etc would be helpful. - Shannon -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Some assistance please
Hi People,
I have a sql query running an array'd result that should echo a different
result depending on the result of the query. However, it successfully does
one of the entries from the query, but then appears to stop. With no further
results displayed. Could someone take a look at the code at the bottom of
this email and see if they can determine why it is not continuing through
the rest of the results.
Thanks in advance
- Shannon
?
$roundnum = "";
$sql = "select * from results order by round";
$dbh = @mysql_connect($dbhost,$dbuser,$dbpass);
$results = mysql_db_query($db,$sql,$dbh);
for($i = 0; $i mysql_num_rows($results); $i++) {
$array[$i] = mysql_fetch_array($results);
}
mysql_close($dbh);
for ($i = 0; $i count($array); $i++) {
if ($array[$i]["round"] != $roundnum) {
$roundnum = $array[$i]["round"];
echo "trtd colspan='5'brp align='center'bfont
color='#99'Round ".$roundnum."/font/b/p/td/tr";
echo "trtd width='100'p".$array[$i]["home"]."/p/tdtd
width='10'p".$array[$i]["home_goals"]."/p/tdtd width='50'\n";
if ($array[$i]["home_goals"] $array[$i]["away_goals"]) {
echo "pDefeated/p/td";
}
elseif ($array[$i]["home_goals"] $array[$i]["away_goals"]) {
echo "pDefeated by/p/td";
}
elseif ($array[$i]["home_goals"] == $array[$i]["away_goals"]) {
echo "pDrew/p/td";
}
echo "td width='100'p".$array[$i]["away"]."/p/tdtd
width='10'p".$array[$i]["away_goals"]."/p/td\n";
}
}
?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Stopping echo
Hi People,
Below I have included some code that just doesn't seem to be functioning all
correctly. I have this running and it appears to stop the query/echo after
the first entry in the database. What is it that I am doing wrong here??
Regards,
Shannon
?
$roundnum = "";
$sql = "select * from results order by round";
$dbh = @mysql_connect($dbhost,$dbuser,$dbpass);
$results = mysql_db_query($db,$sql,$dbh);
for($i = 0; $i mysql_num_rows($results); $i++) {
$array[$i] = mysql_fetch_array($results);
}
mysql_close($dbh);
for ($i = 0; $i count($array); $i++) {
if ($array[$i]["round"] != $roundnum) {
$roundnum = $array[$i]["round"];
echo "trtd colspan='5'brp align='center'bfont
color='#99'Round ".$roundnum."/font/b/p/td/tr";
echo "trtd width='100'p".$array[$i]["home"]."/p/tdtd
width='10'p".$array[$i]["home_goals"]."/p/tdtd width='50'\n";
if ($array[$i]["home_goals"] $array[$i]["away_goals"]) {
echo "pDefeated/p/td";
}
elseif ($array[$i]["home_goals"] $array[$i]["away_goals"]) {
echo "pDefeated by/p/td";
}
elseif ($array[$i]["home_goals"] == $array[$i]["away_goals"]) {
echo "pDrew/p/td";
}
echo "td width='100'p".$array[$i]["away"]."/p/tdtd
width='10'p".$array[$i]["away_goals"]."/p/td\n";
}
}
?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Echo function stopping
Hi People,
Below I have included some code that just doesn't seem to be functioning all
correctly. I have this running and it appears to stop the query/echo after
the first entry in the database. What is it that I am doing wrong here??
Regards,
Shannon
?
$roundnum = "";
$sql = "select * from results order by round";
$dbh = @mysql_connect($dbhost,$dbuser,$dbpass);
$results = mysql_db_query($db,$sql,$dbh);
for($i = 0; $i mysql_num_rows($results); $i++) {
$array[$i] = mysql_fetch_array($results);
}
mysql_close($dbh);
for ($i = 0; $i count($array); $i++) {
if ($array[$i]["round"] != $roundnum) {
$roundnum = $array[$i]["round"];
echo "trtd colspan='5'brp align='center'bfont
color='#99'Round ".$roundnum."/font/b/p/td/tr";
echo "trtd width='100'p".$array[$i]["home"]."/p/tdtd
width='10'p".$array[$i]["home_goals"]."/p/tdtd width='50'\n";
if ($array[$i]["home_goals"] $array[$i]["away_goals"]) {
echo "pDefeated/p/td";
}
elseif ($array[$i]["home_goals"] $array[$i]["away_goals"]) {
echo "pDefeated by/p/td";
}
elseif ($array[$i]["home_goals"] == $array[$i]["away_goals"]) {
echo "pDrew/p/td";
}
echo "td width='100'p".$array[$i]["away"]."/p/tdtd
width='10'p".$array[$i]["away_goals"]."/p/td\n";
}
}
?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Multiple Order By
Hi There, I have a situation that requires me to do a multiple orderby on a MYSQL /PHP query. ie select * from Blah where test='testing' order by field1 then by field2 I am wondering if this will work or is there some other way I can acomplish such a task. The ordering should be decending. Thanks for any help on this. - Shannon -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
