RE: [PHP] help with query
Thanks Jim, worked like a charm.
> -Original Message-
> From: Jim Lucas [mailto:li...@cmsws.com]
> Sent: Thursday, June 07, 2012 4:08 PM
> To: Jack
> Cc: PHP
> Subject: Re: [PHP] help with query
>
> On 06/07/2012 09:37 AM, Jack wrote:
> > $query = "select a.startdate, a.articleid, c.name, a.title, a.intro,
> > a.datecreated from articles as a, categories as c where (a.startdate =
> > -1 or a.startdate<= {$now}) and (a.enddate = -1 or a.enddate>= {$now})
> > and a.categoryid = c.categoryid order by a.startdate DESC";
>
> $query = "
> SELECT a.startdate,
> a.articleid,
> c.name,
> a.title,
> a.intro,
> a.datecreated
> FROMarticles as a,
> categories as c
> WHERE (
> a.startdate = -1
> OR
> a.startdate <= {$now}
> )
> AND (
> a.enddate = -1
> OR
> a.enddate >= {$now}
> )
> -- This line must stay, it is limiting the combination of the data from
> -- both tables
> AND a.categoryid = c.categoryid
> -- You need to add this line to make it work, but keep the previous line
> AND a.categoryid = 1
>
> ORDER BY a.startdate DESC
> ";
>
> --
> Jim Lucas
>
> http://www.cmsws.com/
> http://www.cmsws.com/examples/
> http://www.bendsource.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] help with query
On 06/07/2012 09:37 AM, Jack wrote:
$query = "select a.startdate, a.articleid, c.name, a.title, a.intro,
a.datecreated from articles as a, categories as c where (a.startdate = -1 or
a.startdate<= {$now}) and (a.enddate = -1 or a.enddate>= {$now}) and
a.categoryid = c.categoryid order by a.startdate DESC";
$query = "
SELECT a.startdate,
a.articleid,
c.name,
a.title,
a.intro,
a.datecreated
FROMarticles as a,
categories as c
WHERE (
a.startdate = -1
OR
a.startdate <= {$now}
)
AND (
a.enddate = -1
OR
a.enddate >= {$now}
)
-- This line must stay, it is limiting the combination of the data from
-- both tables
AND a.categoryid = c.categoryid
-- You need to add this line to make it work, but keep the previous line
AND a.categoryid = 1
ORDER BY a.startdate DESC
";
--
Jim Lucas
http://www.cmsws.com/
http://www.cmsws.com/examples/
http://www.bendsource.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] help with query
Jack wrote:
>Hello All,
>
>
>
>I have this string defined for my query and it shows the different
>types of
>categories fine, but when I change a.categoryid = c.categoryid to
>a.categoryid = 1 which is only one of the categories
>
>It shows me the same record twice.
>
>
>
>$query = "select a.startdate, a.articleid, c.name, a.title, a.intro,
>a.datecreated from articles as a, categories as c where (a.startdate =
>-1 or
>a.startdate <= {$now}) and (a.enddate = -1 or a.enddate >= {$now}) and
>a.categoryid = c.categoryid order by a.startdate DESC";
>
>
>
>
>
>while ( $row = mysql_fetch_array($res) ) {
>
>$tpldata['articles'][] = array(
>
>'title' => $row['title'],
>
>'intro' => makeLinks($row['intro']),
>
>'id'=> $row['articleid'],
>
>'categoryname' => $row['name'],
>
>'created' => date('n/j/Y',
>$row['datecreated'])
>
>);
>
>//echo $row['datecreated'];
>
>}
>
>
>
>Any Ideas???
>
>
>
>
>
>Thanks!
>
>
How many entries in total are in your categories table? You're joining the
table but not putting any kind of constraint on the joined tables, so its doing
a join on all the rows. You can either add an extra clause on the join, leave
the original join and limit with a where clause, or use a group by on that
field.
Thanks,
Ash
http://ashleysheridan.co.uk
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] help with query
Hello All,
I have this string defined for my query and it shows the different types of
categories fine, but when I change a.categoryid = c.categoryid to
a.categoryid = 1 which is only one of the categories
It shows me the same record twice.
$query = "select a.startdate, a.articleid, c.name, a.title, a.intro,
a.datecreated from articles as a, categories as c where (a.startdate = -1 or
a.startdate <= {$now}) and (a.enddate = -1 or a.enddate >= {$now}) and
a.categoryid = c.categoryid order by a.startdate DESC";
while ( $row = mysql_fetch_array($res) ) {
$tpldata['articles'][] = array(
'title' => $row['title'],
'intro' => makeLinks($row['intro']),
'id'=> $row['articleid'],
'categoryname' => $row['name'],
'created' => date('n/j/Y',
$row['datecreated'])
);
//echo $row['datecreated'];
}
Any Ideas???
Thanks!
[PHP] Help with query MySQL and PHP
Hi,
I want to create a new column or table from queries of the values of
columns I( already have.
My table is :
PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS
products_id products_extra_fields_id products_extra_fields_value
1
1 Blond
1
2 Green
1
3 1.75
1
4 24
2
1 Brunette
2
2 Hazel
2
3 1.56
2
4 28
with this Select query >>>
function get_extra($extra_array = '') {
if (!is_array($extra_array)) $extra_array = array();
$extra_query = db_query("select products_extra_fields_value from " .
TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " where
products_extra_fields_id = '1'");
while ($extra = db_fetch_array($extra_query)) {
$extra_array[] = array('id' => $extra['products_extra_fields_id'],
'text' => $extra['products_extra_fields_value']);
}
return $extra_array;
}
This function uses the query: select products_extra_fields_value from
PRODUCTS_EXTRA_FIELDS where products_extra_fields_id = 1;
and returns Rubia and Morocha which is fine, now I need to
either create a new column or table in order to be able to have those
values in their own column
my knowledge of mysql is limited so please give me suggestions as what
to use in order to create a function to retrieve values and add them
automatically to my table or columns
The table or columns have to be something like:
ID Hair Eyes
Blond Green
Brunette Hazel
so I can call them later by selecting from a column in a table like
TABLE_PRODUCTS_EXTRA_FIELDS_TO_PRODUCTS_EXTRA_FIELDS_VALUE
pef2pev.hair, pef2pev.eyes ... and so on
here is my current switch
for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
switch ($column_list[$i]) {
case 'PRODUCT_LIST_MODEL':
$select_column_list .= 'p.products_model, ';
break;
case 'PRODUCT_LIST_MANUFACTURER':
$select_column_list .= 'm.manufacturers_name, ';
break;
// aDD eXTRA FIELdS
case 'PRODUCT_LIST_HAIR':
$select_column_list .= 'pef2pev.hair, ';
break;
and so on ...
break;
}
}
Thanks
Rick Fitzgerald
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Help with query
Hi I need the students that didn't take an exam. The tables: exams(id_test, title, desciption, ) results(id_student, id_test, date, qualification...) I'm using a version of MySQL that doesn't support NOT IN, then I tried in this way: SELECT * FROM exams LEFT JOIN results ON exams.id_test=results.id_test WHERE results.id_test IS NULL AND id_student=".$user The query doesn't return anything... What Can I do??? Ahead of time, thank you very much, Tom. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
SV: [PHP] Help with query
Thanks guys! - Daniel -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help with query
> Hi, > > i can?t seem to get my head to wake up... > > How would i do to count how many different values that exists in a mysql > field? Ie i have a date-field and need to know how many different dates that > exists in the table. select count(distinct name) from site; in Mysql Valentin Petruchek (aki Zliy Pes) *** Cut the beginning *** http://zliypes.com.ua mailto:[EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help with query
$number=mysql_num_rows(mysql_query("SELECT DISTINCT date FROM $table"));
Op zaterdag 09 februari 2002 13:41, schreef Daniel Alsén:
> Hi,
>
> i can´t seem to get my head to wake up...
>
> How would i do to count how many different values that exists in a mysql
> field? Ie i have a date-field and need to know how many different dates
> that exists in the table.
>
> Regards
> # Daniel Alsén| www.mindbash.com #
> # [EMAIL PROTECTED] | +46 704 86 14 92 #
> # ICQ: 63006462 | +46 8 694 82 22 #
> # PGP: http://www.mindbash.com/pgp/ #
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Help with query
Hi, i can´t seem to get my head to wake up... How would i do to count how many different values that exists in a mysql field? Ie i have a date-field and need to know how many different dates that exists in the table. Regards # Daniel Alsén| www.mindbash.com # # [EMAIL PROTECTED] | +46 704 86 14 92 # # ICQ: 63006462 | +46 8 694 82 22 # # PGP: http://www.mindbash.com/pgp/ # -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
