php-general Digest 21 Nov 2010 17:03:07 -0000 Issue 7047

[php-general-digest-help]

php-general Digest 21 Nov 2010 17:03:07 -0000 Issue 7047

Topics (messages 309590 through 309594):

Re: MySQL Query Help
        309590 by: Richard West
        309591 by: admin.buskirkgraphics.com
        309593 by: Simcha Younger
        309594 by: PW

Problem with functions and arrays...
        309592 by: Jason Pruim

Administrivia:

To subscribe to the digest, e-mail:
        php-general-digest-subscr...@lists.php.net

To unsubscribe from the digest, e-mail:
        php-general-digest-unsubscr...@lists.php.net

To post to the list, e-mail:
        php-gene...@lists.php.net


----------------------------------------------------------------------

--- Begin Message ---

I'm going to jump in and throw in my 2 cents...

Have you used dreamweaver?
I would suggest Dreamweaver to any new programmer beginning php/mysql.
It helped me out tremendously in the beginning. I'm not an advanced programmer 
with hand coding classes yet, but I can get any job completed for clients with 
dreamweaver. Custom content management systems, image galleries from mysql 
etc...

Give it a try, It lets you add the the prewritten code and then you can switch 
to code view and see whats its doing.
RD

On Nov 20, 2010, at 3:54 PM, Ben Miller wrote:

> Hi,
> 
> I'm building a website for a client in which I need to compare their
> products, side-by-side, but only include criteria for which all selected
> products have a value for that criteria.
> 
> In my database (MySQL), I have a tables named "products","criteria" and
> "criteria_values"
> 
> If I have something like
> 
> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...)  //  All
> products selected for comparison by the user
> 
> I need to get only rows from "criteria" where there is a row in
> "criteria_values" matching "criteria.criteria_id" for each $selected_product
> - in other words, if any of the $selected_product does not have a row in
> "criteria_values" that matches "criteria.criteria_id", that criteria would
> not be returned.  I hope that makes sense.
> 
> I've played around with a few join queries, but none have given the desired
> results.  Best I've been able to come up with so far is to query "criteria"
> for each DISTINCT(criteria_id) and then run through each $selected_product
> to make sure each has a criteria_value with a matching criteria_id,
> eliminating any criteria where the number of criteria_values <
> count($selected_product), but this seems pretty inefficient.
> 
> Thanks in advance for any help.
> 
> Ben Miller
> 
> 
> -- 
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

--- End Message ---

--- Begin Message ---

Since we are just tossing out development environments.

We moved to Aptana in conjunction with TortoiseSVN for a team environment
development timelines dropped.  
Personally I do not feel any gui editor makes you a better programmer, maybe
you understand the fundamentals a little less.


Not that anything so far has been an answer to your question.

Developing mysql statements that not only require minimum resources but that
are highly effective. Take a lot of trial and error.
I feel there is no better tool in my mind to test query concepts than
NaviCat.

Not only is the product very user friendly but as a senior developer it
gives me more insight into the impact the query has on my servers.
Always think longevity of the product you are producing. 
Imagine one day you have 650,000 products how will the query impact the
service you have written.

I feel as a certified MySQL DBA you should understand that what works today
may NOT be the best choice in query statements for the future.
Always analyze your query statements for query length and system resources
requirements.

Depending on the structure of your database/tables/fields your query may be
achieved many different ways.

I might suggest you try an extended select statement.

 "SELECT product from sometable WHERE product='$array1' AND product=(SELECT
product_name from sometable where other matching critera)"; 

By extending or what some may call concating the statement the return is
more effective.



Richard L. Buskirk



-----Original Message-----
From: Ben Miller [mailto:biprel...@gmail.com] 
Sent: Saturday, November 20, 2010 3:54 PM
To: 'php-general'
Subject: [PHP] MySQL Query Help

Hi,

I'm building a website for a client in which I need to compare their
products, side-by-side, but only include criteria for which all selected
products have a value for that criteria.

In my database (MySQL), I have a tables named "products","criteria" and
"criteria_values"

If I have something like

$selected_product = array("1"=>"Product 1","2"=>"Product 2"...)  //  All
products selected for comparison by the user

I need to get only rows from "criteria" where there is a row in
"criteria_values" matching "criteria.criteria_id" for each $selected_product
- in other words, if any of the $selected_product does not have a row in
"criteria_values" that matches "criteria.criteria_id", that criteria would
not be returned.  I hope that makes sense.

I've played around with a few join queries, but none have given the desired
results.  Best I've been able to come up with so far is to query "criteria"
for each DISTINCT(criteria_id) and then run through each $selected_product
to make sure each has a criteria_value with a matching criteria_id,
eliminating any criteria where the number of criteria_values <
count($selected_product), but this seems pretty inefficient.

Thanks in advance for any help.

Ben Miller


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--- End Message ---

--- Begin Message ---

On Sat, 20 Nov 2010 13:54:29 -0700
"Ben Miller" <biprel...@gmail.com> wrote:

> Hi,
> 
> I'm building a website for a client in which I need to compare their
> products, side-by-side, but only include criteria for which all selected
> products have a value for that criteria.
> 
> In my database (MySQL), I have a tables named "products","criteria" and
> "criteria_values"
> 
> If I have something like
> 
> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...)  //  All
> products selected for comparison by the user
> 
> I need to get only rows from "criteria" where there is a row in
> "criteria_values" matching "criteria.criteria_id" for each $selected_product
> - in other words, if any of the $selected_product does not have a row in
> "criteria_values" that matches "criteria.criteria_id", that criteria would
> not be returned.  I hope that makes sense.

It would be a lot easier to think about this if you could provide the table 
structure or create table statements.

If I understood correctly, you have products which reference a criteria ID 
which has no matching value. If this is the problem you have a to first take 
care of the integrity of your data, as this should never happen. 


-- 
Simcha Younger <sim...@syounger.com>

--- End Message ---

--- Begin Message ---

SELECT * FROM products p LEFT JOIN criteria_values cv ON p.key=cv.key LEFT JOIN 
criteria c ON cv.key=c.key WHERE c.value IS NOT NULL

Hard to answer without more detail, but I am guessing the answer will be 
something like the above. Your question makes it hard to understand whether c 
or cv is joined to p. So swap em around if I misunderstood. 

iPhone 4. It rocks!

On Nov 21, 2010, at 1:37 AM, Simcha Younger <sim...@syounger.com> wrote:

> On Sat, 20 Nov 2010 13:54:29 -0700
> "Ben Miller" <biprel...@gmail.com> wrote:
> 
>> Hi,
>> 
>> I'm building a website for a client in which I need to compare their
>> products, side-by-side, but only include criteria for which all selected
>> products have a value for that criteria.
>> 
>> In my database (MySQL), I have a tables named "products","criteria" and
>> "criteria_values"
>> 
>> If I have something like
>> 
>> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...)  //  All
>> products selected for comparison by the user
>> 
>> I need to get only rows from "criteria" where there is a row in
>> "criteria_values" matching "criteria.criteria_id" for each $selected_product
>> - in other words, if any of the $selected_product does not have a row in
>> "criteria_values" that matches "criteria.criteria_id", that criteria would
>> not be returned.  I hope that makes sense.
> 
> It would be a lot easier to think about this if you could provide the table 
> structure or create table statements.
> 
> If I understood correctly, you have products which reference a criteria ID 
> which has no matching value. If this is the problem you have a to first take 
> care of the integrity of your data, as this should never happen. 
> 
> 
> -- 
> Simcha Younger <sim...@syounger.com>
> 
> -- 
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

--- End Message ---

--- Begin Message ---

Hey Everyone!

So I came across a problem that I don't know how to fix... I have searched and thought and just not having anything click as to where I am messing up...

I have a few functions as follows:
<?PHP

function ddbYear($name, $message, $_POST, $option){

    //Make sure to post form start/stop OUTSIDE of this function...
    //It's not meant to be a one size fits all function!
echo "NAME: " . $name . "<BR>";
echo "MESSAGE: " . $message . "<BR>";

echo "POST: " . $_POST . "<BR>";
echo "OPTION: " . $option . "<BR>";


$sticky = '';
if(isset($_POST['submit'])) {
$sticky = $_POST["{$name}"];
echo "STICKY: " . $sticky;
}
//echo "OPTION: ";
//print_r($option);

    echo <<<HTML
<select name="{$name}">
<option value="0">{$message}</option>

HTML;

foreach ($option as $key => $value){

if($key == $sticky) {
echo '<option value="' . $key .'" selected>' . $value . '</option>';
}else{
echo '<option value="' . $key .'">' . $value . '</option>';
}

}

echo <<<HTML

</select>
HTML;
unset($value);
return;
}

?>

One for Month, Day & Year... All the same exact code... When I get brave I'll combine it into 1 functions :)

Now... What it's trying to do.. It's on a "update" form on my website. Basically pulls the info from the database and displays it in the form again so it can be edited and resubmitted...

As I'm sure you can tell from the function it checks to see if the a value has been selected in the drop down box and if it has then set the drop down box to that value.

I call the function like this:
<?PHP
$startYear = date("Y", $row['startdate']); //Actual DBValue: 1265000400

$optionYear = array("2010" => "2010", "2011" => "2011", "2012" => "2012", "2013" => "2013", "2014" => "2014");

ddbYear("startYear", "Select Year", $startYear, $optionYear);


?>

The output I'm getting is:

THESE ARE THE ACTUAL UNPROCESSED (OTHER THEN SEPARATING) VALUES FROM THE DATABASE

startmonth: 2
startday: 1
startYear: 2010
endmonth: 11
endDay: 30
endYear: 1999

THESE ARE THE VALUES INSIDE THE FUNCTION
NAME: startYear
MESSAGE: Select Year
POST: 2010
OPTION: Array
STICKY: 2

Now... The problem is that $sticky get set to "2" instead of "2010"... But I can't figure out why...

Anyone have any ideas?

And just incase I didn't provide enough info here's a link that shows it happening:

HTTP://jason.pruimphotography.com/dev/cms2/events/update_form.php?id=62

Thanks for looking and for your answers in advance! :)

--- End Message ---