Page 1 of 1

How to change MySQL table values before echo?

Posted: Sat Sep 25, 2010 2:28 pm
by Iluvatar
Hello, I'm pretty green when it comes to php, but have successfully constructed a script that almost does what I want it to do. I have posted the code below.

Code: Select all

<?
$username="xxxxxx";
$password="xxxxxxx";
$database="xxxxxxx";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT products_description.products_name, products_description.extra_value3, products_description.extra_value4, products_description.extra_value6, products_description.extra_value7, products_description.extra_value_id12, products_description.extra_value_id13, products.products_price ".
 "FROM products_description, products ".
 "WHERE products_description.products_id = products.products_id ".
 "ORDER BY products_description.products_name ";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
    echo $row['products_name']. ", ". 
$row['extra_value3']. ", ". $row['extra_value4']. ", ". $row['extra_value6']. ", ". $row['extra_value7']. ", ". $row['extra_value_id12']. "/". $row['extra_value_id13']. " - ". $row['products_price'];
    echo "<br />";
}

?>
However, the problem comes with extra_value_id12 and extra_value_id13. They only show numbers, when they are supposed to show letters (vinyl record grades).

I want to do something like what I have posted below, I just havent found a way to implement it, everytime I've tried I either get errors or empty fields:

Code: Select all

if ($extra_value_id12 == "7" ) {
    echo "SEALED";
} elseif ($extra_value_id12 == "8"){
    echo "NM";
} elseif ($extra_value_id12 == "9"){
    echo "EX";
} elseif ($extra_value_id12 == "10"){
    echo "VG+";
} elseif ($extra_value_id12 == "11"){
    echo "VG";
} elseif ($extra_value_id12 == "12"){
    echo "VG-";
} elseif ($extra_value_id12 == "13"){
    echo "G";
} elseif ($extra_value_id12 == "14"){
    echo "P";
} else {
    echo "";
}


if ($extra_value_id13 == "15" ) {
    echo "SEALED";
} elseif ($extra_value_id13 == "16"){
    echo "NM";
} elseif ($extra_value_id13 == "17"){
    echo "EX";
} elseif ($extra_value_id13 == "18"){
    echo "VG+";
} elseif ($extra_value_id13 == "19"){
    echo "VG";
} elseif ($extra_value_id13 == "20"){
    echo "VG-";
} elseif ($extra_value_id13 == "21"){
    echo "G";
} elseif ($extra_value_id13 == "22"){
    echo "P";
} else {
    echo "";
}
Does anyone have any suggestions or helpful tips? Thanks!

Re: How to change MySQL table values before echo?

Posted: Sat Sep 25, 2010 6:12 pm
by mecha_godzilla
You could do this a couple of ways, but what you aren't doing when you loop through the $result array is assign the values to variables (which you'll need to if you want to test them). Where your code looks like this:

Code: Select all

while($row = mysql_fetch_array($result)){
    echo $row['products_name']. ", ".
    $row['extra_value3']. ", ". $row['extra_value4']. ", ". $row['extra_value6']. ", ". $row['extra_value7']. ", ". $row['extra_value_id12']. "/". $row['extra_value_id13']. " - ". $row['products_price'];
    echo "<br />";
}
change it so it looks like this:

Code: Select all

while($row = mysql_fetch_array($result)){

	$extra_value_id12 = $row['extra_value_id12'];
	$extra_value_id13 = $row['extra_value_id13'];
	
    echo $row['products_name']. ", ".
    $row['extra_value3']. ", ". $row['extra_value4']. ", ". $row['extra_value6']. ", ". $row['extra_value7']. ", ". $row['extra_value_id12']. "/". $row['extra_value_id13']. " - ". $row['products_price'];
    echo "<br />";
}
You can then test the values inside the while() loop using this code:

Code: Select all

switch ($extra_value_id12) {
    case '7':
        $this_value = 'SEALED';
        break;
    case '8':
        $this_value = 'NM';
        break;
    default:
        // do nothing
        break;
}
I think switch() will be quicker than a bunch of if/else if statements but don't quote me on that :mrgreen:

An alternative, more robust(?) way would be to create two new DB tables to correspond with the $extra_value_id12 and $extra_value_id13 values, then JOIN them in your query. As an example, create a table called extra_value_id12_TABLE then add two fields to it called extra_value_id12_ID and extra_value_id12_DESC. When you add new records to this table, make sure that you specify the value for extra_value_id12_ID or it will probably start at 1 and increment upwards.

To retrieve your values, you could then use this sort of query:

Code: Select all

SELECT products_description.products_name, products_description.extra_value3, products_description.extra_value4, products_description.extra_value6, products_description.extra_value7,products.products_price, extra_value_id12_TABLE.extra_value_id12_DESC, extra_value_id13_TABLE.extra_value_id13_DESC
FROM products_description, products
JOIN extra_value_id12_TABLE on extra_value_id12_TABLE.extra_value_id12_ID = products_description.extra_value_id12
JOIN extra_value_id13_TABLE on extra_value_id13_TABLE.extra_value_id13_ID = products_description.extra_value_id13
WHERE products_description.products_id = products.products_id
ORDER BY products_description.products_name 
I can't guarantee that will work because it's dependent on how you set the new tables up, but you should be able to see what's happening at least. With this query, you only get the descriptions and not the numbers but you have to modify your code inside the while() loop so it echoes out the right values.

If you need any more help just say so.

Mecha Godzilla

Re: How to change MySQL table values before echo?

Posted: Sun Oct 03, 2010 4:50 pm
by Iluvatar
Hello Mecha Godzilla, thanks for the advice. I tried what you suggested, but still couldnt get it to work. :?

Here is what my code currently looks like:

Code: Select all

<?
$username="xxxxx";
$password="xxxxx";
$database="xxxxxxx";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT products_description.products_name, products_description.extra_value3, products_description.extra_value4, products_description.extra_value6, products_description.extra_value7, products_description.extra_value_id12, products_description.extra_value_id13, products.products_price ".
 "FROM products_description, products ".
 "WHERE products_description.products_id = products.products_id ".
 "ORDER BY products_description.products_name ";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){


        $extra_value_id12 = $row['extra_value_id12'];

switch ($extra_value_id12) {
    case '7':
        $this_value = 'SEALED';
        break;
    case '8':
        $this_value = 'NM';
        break;
    case '9':
        $this_value = 'EX';
        break;
    case '10':
        $this_value = 'VG+';
        break;
    case '11':
        $this_value = 'VG';
        break;
    case '12':
        $this_value = 'VG-';
        break;
    case '13':
        $this_value = 'G';
        break;
    case '14':
        $this_value = 'P';
        break;
    default:
        // do nothing
        break;
}
        $extra_value_id13 = $row['extra_value_id13'];

switch ($extra_value_id13) {
    case '15':
        $this_value = 'SEALED';
        break;
    case '16':
        $this_value = 'NM';
        break;
    case '17':
        $this_value = 'EX';
        break;
    case '18':
        $this_value = 'VG+';
        break;
    case '19':
        $this_value = 'VG';
        break;
    case '20':
        $this_value = 'VG-';
        break;
    case '21':
        $this_value = 'G';
        break;
    case '22':
        $this_value = 'P';
        break;
    default:
        // do nothing
        break;
}

     
    echo $row['products_name']. ", ".
    $row['extra_value3']. ", ". $row['extra_value4']. ", ". $row['extra_value6']. ", ". $row['extra_value7']. ", ". $row['extra_value_id12']. "/". $row['extra_value_id13']. " - ". $row['products_price'];
    echo "<br />";
}


?>


Suggestions? I tried putting the switch statement in different positions, but it still didnt help :/ Help is much appreciated.

Re: How to change MySQL table values before echo?

Posted: Sun Oct 03, 2010 7:00 pm
by mecha_godzilla
Hi again,

Where your code looks like this towards the end of the script

Code: Select all

echo $row['products_name']. ", ".
$row['extra_value3']. ", ". $row['extra_value4']. ", ". $row['extra_value6']. ", ". $row['extra_value7']. ", ". $row['extra_value_id12']. "/". $row['extra_value_id13']. " - ". $row['products_price'];
echo "<br />";
you need to change it so it looks like

Code: Select all

echo $row['products_name']. ", ".
$row['extra_value3']. ", ". $row['extra_value4']. ", ". $row['extra_value6']. ", ". $row['extra_value7']. ", ". $this_value . "/". $another_value. " - ". $row['products_price'];
echo "<br />";
because otherwise your script just echo's out the old values. Also, in the two switch() statements you need to make sure that the variables are different - you're using $this_value for both the extra_value_id12 and extra_value_id13 values. In case the values aren't being evaluated properly in the switch() statements for some reason, just add a value in the 'default:' option such as

Code: Select all

default:
    $this_value = 'TEST';
    break;
HTH,

Mecha Godzilla