Selectivly Exclude MYsql entries

Codewalkers Forums Sponsor:

November 4th, 2009, 05:17 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

php5 - Selectivly Exclude MYsql entries

so i have a database call where i am excluding specific records (based on a field) and the solution i have works but seems kludgy, anyone know how i can streamline it?

PHP Code:


		
			
 $querystring3='SELECT * FROM Issues WHERE Status < 3 AND Number != 0 AND Number != 4 AND Number != 7 ORDER BY Number ASC';

__________________
29 years of creative writing
13 years of HTML
10 years of Photoshop
6 years of PHP/MySQL
And I never knew Photoshop could do HTML until 2004!
You learn something new every day.

November 4th, 2009, 06:17 PM

MatthewJ

Contributing User

Join Date: May 2007

Location: Davenport, Iowa

Posts: 564

Time spent in forums: 1 Week 21 h 23 m 29 sec
Reputation Power: 3

PHP Code:


		
			
 $status = array(3, 0, 4, 7);



$querystring3 = "SELECT * FROM Issues WHERE Status IN ($status) ORDER BY Number ASC";

That should work... not sure if it would be much faster, but it does clean it up

November 4th, 2009, 06:20 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

Quote:

Originally Posted by MatthewJ

PHP Code:


		
			
 $status = array(3, 0, 4, 7);



$querystring3 = "SELECT * FROM Issues WHERE Status IN ($status) ORDER BY Number ASC";

That should work... not sure if it would be much faster, but it does clean it up

so the IN operator what excludes the values set?

November 4th, 2009, 08:28 PM

MatthewJ

Contributing User

Join Date: May 2007

Location: Davenport, Iowa

Posts: 564

Time spent in forums: 1 Week 21 h 23 m 29 sec
Reputation Power: 3

Heh... good point, I'm used to looking for an item in the array... maby NOT IN?

November 5th, 2009, 03:07 AM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

ya, not in should work.

SELECT * FROM SomeTable WHERE Number NOT IN (0,4,5,7)

or if those numbers change and you want to exclude specific rows from another table where certain criteria in that other table is met you can also use NOT IN with a sub query as long as the sub query returns just one column:

SELECT * FROM SomeTable WHERE ForeignNumber NOT IN (SELECT TableKey FROM SomeOtherTable WHERE SomeColumn=1)

Last edited by IAmALlama : November 5th, 2009 at 03:24 AM.

November 5th, 2009, 04:55 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

Thanks! Very helpful.

In the same vein though i have 2 other bits of code that are kinda tricky

PHP Code:


		
			
 $querystring3a='SELECT * FROM Issues WHERE Status < 3 AND Number = 7';

$querystring3b='SELECT * FROM Issues WHERE Status < 3 AND Number = 1 OR Number = 6 ORDER BY Number ASC';

$querystring3c='SELECT * FROM Issues WHERE Status < 3 AND Number = 3 OR Number = 5 OR Number = 8 ORDER BY Number ASC';

$IssueArray = mysql_fetch_assoc(mysql_query($querystring3a));

echo DynamicPicReturn($uploadsISS.$IssueArray['Image'], 180).'<BR>';

$run3b = querya($querystring3b);

foreach($run3b as $IssueArray){echo DynamicPicReturnH($uploadsISS.$IssueArray['Image'], 112);};

echo '<BR>';

$run3c = querya($querystring3c);

foreach($run3c as $IssueArray){echo DynamicPicReturnH($uploadsISS.$IssueArray['Image'], 74);};

I figured i could use IN here by my concern is i need the images in a specific order.
The first one uses the DynamicPicsReturn 180 call
the next two uses the PicsReturnH 112 call
the last 3 use the picsreturnH 74 call

in another file i have a if loop thats a bit tricky

PHP Code:


		
			
if ($MainArray2['ContentID'] == $_POST['AddtlBylineType'][$MainArray2['ContentID']]){};

Basicly what i want to do is compare the valeu of mainarray 2 to the index key of the post

Basicly something like

42 == $_POST['AddtlBylineType'][42]

Here's the full function to give you a idea what im trying to do

PHP Code:


		
			
if ($MainArray2['ContentID'] == $_POST['AddtlBylineType'][$MainArray2['ContentID']]){

//update    

if ($MainArray2['BylineType'] != $_POST['AddtlBylineType'][$MainArray2['ContentID']]){$update = mysql_query("UPDATE ExtraBylines SET BylineType = '".$_POST['AddtlBylineType'][$MainArray2['ContentID']]."' WHERE ContentID ='".$_GET['ID']."'")or die(mysql_error());}

if ($MainArray2['BylineID'] != $_POST['AddtlBylineID'][$MainArray2['ContentID']]){$update = mysql_query("UPDATE ExtraBylines SET BylineID = '".$_POST['AddtlBylineID'][$MainArray2['ContentID']]."' WHERE ContentID ='".$_GET['ID']."'")or die(mysql_error());}

}

Last edited by LLX : November 5th, 2009 at 05:03 PM.

Viewing: Codewalkers Forums > PHP Related > PHP Coding > php5 - Selectivly Exclude MYsql entries

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.
Request Your Free Technology Downloads!

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.
Request Your Free Technology Downloads!

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.
Request Your Free Technology Downloads!

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.
Request Your Free Technology Downloads!

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.
Request Your Free Technology Downloads!

More Free IT White Papers!