Page 1 of 1

If/else in where statement

Posted: Tue Sep 04, 2012 2:15 am
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>";
?>

Re: If/else in where statement

Posted: Tue Sep 04, 2012 4:40 am
by Celauran
Sounds like poor database design, to be honest. You'd do better to have that as three separate rows.

Re: If/else in where statement

Posted: Tue Sep 04, 2012 4:53 pm
by dk2009
Hi Celauran, thanks for answering.

But... I need it to only show values from one of the "company" columns - what to do? ;-)

Re: If/else in where statement

Posted: Tue Sep 04, 2012 7:48 pm
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.

Re: If/else in where statement

Posted: Wed Sep 05, 2012 2:38 am
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

Re: If/else in where statement

Posted: Wed Sep 05, 2012 11:55 am
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.

Re: If/else in where statement

Posted: Thu Sep 06, 2012 1:23 am
by dk2009
Ok, i will.

Thanks for taking the time to explain and finding the links..

Re: If/else in where statement

Posted: Mon Sep 10, 2012 2:47 am
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%')
";

Re: If/else in where statement

Posted: Mon Sep 10, 2012 11:32 am
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.