[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!
Re: [PHP] help with query
Jack jacklistm...@gmail.com 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
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
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
[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: DOMXPath-Query, quotes and apostrophes
I seem to be hitting a wall here. I have the need to run an XPath query
on strings that may contain quotes, apostrophes or both. Going through
google turned up some interesting information, but nothing that solved
my issue.
An example of what I am trying to do is as follows:
$query = '\'foobar';
$xpath-query('/[EMAIL PROTECTED]'.$query.']')-item(0);
This produces these warnings and errors:
Warning: DOMXPath::query() [function.DOMXPath-query]: Invalid predicate
Warning: DOMXPath::query() [function.DOMXPath-query]: Invalid expression
Fatal error: Call to a member function item() on a non-object
I've tried to escape the characters in the query, but that seems to be
illegal for XPath from what I've read. I've tried turning the quotes
into quot; and apostrophes into apos; but it appears that XPath
requires that the data be unencoded to work.
I've seen reference to using XSLT to set XPath variables instead, but I
don't understand how to do implement this.
I would really appreciate any examples on what people have done to get
around this issue, if any one has at all.
Regards,
Adam.
--
Adam Randall [EMAIL PROTECTED]
(206) 285-8080
100 West Harrison
North Tower, Suite 300
Seattle, WA
98119
Engineers like to solve problems. If there are no problems handily
available, they will create their own problems. - Dilbert
--
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
[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
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
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
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
