If/else in where statement

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dk2009
Forum Newbie
Posts: 5
Joined: Tue Sep 04, 2012 2:00 am

If/else in where statement

Post by dk2009 »

Hi all.

I´m a newbie and need modifications to this scipt.
I have the following columns in my sql-database: sitename1, sitename2, sitename3, product, company1, company2, company3, information, price.

I need the script to output company1 if the WHERE statement is site1, company2 if the WHERE statement is site2 etc.

Conclusion: it should output values from different columns which matches an WHERE statement - maybe is very basic, but remember... i´m a newbie ;-)

Code: Select all

<?
include("databaseinfo.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$title = get_the_title(); 
    $protocol = strpos(strtolower($_SERVER['SERVER_PROTOCOL']),'https') 
                    === FALSE ? 'http' : 'https';
    $host     = $_SERVER['HTTP_HOST'];
    $currentUrl = $protocol . '://' . $host;
$query="SELECT * FROM mypricetable WHERE sitename1 LIKE '%$currentUrl%' AND product LIKE '$title'";
$result=mysql_query($query);
$num=mysql_numrows($result); 
mysql_close();
?>

<table>
<tr> 
<th style="text-align: left;">Company 1</th>
<th style="text-align: left;">Information</th>
<th style="text-align: right;">Price</th>
</tr>

<?
$i=0;
while ($i < $num) {
$company1=mysql_result($result,$i,"company1");
$information=mysql_result($result,$i,"information");
$price=mysql_result($result,$i,"price");
?>

<tr> 
<td><? echo "$company1"; ?></td>
<td><? echo "$information"; ?></td>
<td><? echo "$price"; ?></td>
</tr>

<?
++$i;
} 
echo "</table>";
?>
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: If/else in where statement

Post by Celauran »

Sounds like poor database design, to be honest. You'd do better to have that as three separate rows.
dk2009
Forum Newbie
Posts: 5
Joined: Tue Sep 04, 2012 2:00 am

Re: If/else in where statement

Post by dk2009 »

Hi Celauran, thanks for answering.

But... I need it to only show values from one of the "company" columns - what to do? ;-)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: If/else in where statement

Post by califdon »

To answer a question about a database query, it is essential to know how your table(s) is(are) structured. I will venture to guess that you have one table that is structured more or less like this:
[text]mypricetable:
  company
   site
product
   information
   price[/text]

Does the table have a primary key field? If not, and especially if there is a relation between the company and the site, you don't have a valid relational database. Database structure is nothing like a spreadsheet, it must follow very strict rules dealing with such things as whether every non-key field is dependent solely on the key field, etc. If this is not true, you will not get the results you want from SQL, which assumes that the rules of relational database structure have been followed. You might want to read a basic tutorial on relational database design, such as: http://www.dreamincode.net/forums/topic ... alization/ or http://www.anchor.com.au/hosting/suppor ... alDatabase or http://www.surfermall.com/relational/lesson_1.htm.
dk2009
Forum Newbie
Posts: 5
Joined: Tue Sep 04, 2012 2:00 am

Re: If/else in where statement

Post by dk2009 »

Hi.

The php-scripy works fine as it is, but i need it to echo value from different columns in my sql-database according to which where statement matches.

If the Where statement matches option 1 my html-table should echo the value under the sql column Company1 in the html-column Company and if it matches option 2 it should echo the values under the sql column Company2 (still under the html table column Company).

My sql table have an unique ID column with number and the following columns: sitename1, sitename2, sitename3, product, company1, company2, company3, information, price
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: If/else in where statement

Post by califdon »

dk2009 wrote:My sql table have an unique ID column with number and the following columns: sitename1, sitename2, sitename3, product, company1, company2, company3, information, price
That is your problem. Your database fails to meet even First Normal Form of the relational model. You will not be able to get results that you want from a SQL query with such a data structure. Your only choice is to correct your database structure. Take my advice and read the references I provided in my earlier post.
dk2009
Forum Newbie
Posts: 5
Joined: Tue Sep 04, 2012 2:00 am

Re: If/else in where statement

Post by dk2009 »

Ok, i will.

Thanks for taking the time to explain and finding the links..
dk2009
Forum Newbie
Posts: 5
Joined: Tue Sep 04, 2012 2:00 am

Re: If/else in where statement

Post by dk2009 »

Solved.
This do the trick:

$query="
SELECT product, information, price,
Case When sitename1 LIKE '%$currentUrl%' Then company1
When sitename2 LIKE '%$currentUrl%' Then company2
When sitename3 LIKE '%$currentUrl%' Then company3
End As company
From YOURTABLENAME
Where product LIKE '$title'
And (sitename1 LIKE '%$currentUrl%' Or sitename1 LIKE '%$currentUrl%' Or sitename1 LIKE '%$currentUrl%')
";
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: If/else in where statement

Post by califdon »

Yes, that will work in this one limited example, but it is not standard SQL that is supported by all database engines and is not in conformance with the relational model, so it would be inefficient if the table were extremely large. If it solves your immediate problem, go for it, but do yourself a big favor and understand that this is extremely poor practice and that if you are ever going to use databases in other applications, you MUST learn how the relational model works and be able to structure your data as a relational database, using multiple tables. As long as you merely find something that happens to work in your particular application, you are inviting more serious problems ahead.
Post Reply