解析一个字段以填充MySQL中的另一个字段

Question

I am new to PHP/MySQL and am working my way through the basics.

I have a MySQL database scwdb (that I moved from Access 2000 which my Windows 7 won't work with) with a table tblsplintersbowlinventory which has 2 fields:

fields and data:

txtProductBowlCode
data examples: OakSc07-001, MapleTi07-030, MapleTi07-034, BlackLimba07-002, AshSc07-017

txtProductPrimarySpecies
data examples: Oak, Maple, Maple, BlackLimba, Ash

In other words, I want to record just the species in the txtProductPrimarySpecies field.

I tried the following PHP script:

    <?php
$con = mysql_connect("localhost","xxxxxxx","zzzzzzz");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("scwdb", $con);

$species = 'Maple';
mysql_query("UPDATE tblsplintersbowlinventory WHERE txtProductBowlCode LIKE $species SET txtProductPrimarySpecies=$species%");
echo "done";

mysql_close($con);
?>

It seems to run, does not show an error and prints "done", but when I check the database I don't see any changes.

What am I missing?

This db has over 600 records, and I added this new txtProductPrimarySpecies field to make my searches easier while leaving the full code which has specific info on the bowl. There are several species that I need to do this to, so I plan on using a loop to run through a list of species.

How would I code that loop to read a list of species?

OK, I found the way to make this work!

    <?php
$con = mysql_connect("localhost","xxxxxx","zzzzzzz");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("scwdb", $con);

$species = 'Maple';
$result = mysql_query("UPDATE tblsplintersbowlinventory SET txtProductPrimarySpecies = '$species' WHERE txtProductBowlCode LIKE '$species%'");
$result = mysql_query("SELECT * FROM tblsplintersbowlinventory WHERE txtProductBowlCode LIKE '$species%'");

echo "<table border='1'>
<tr>
<th>Index</th>
<th>Bowl Code</th>
<th>Species</th>
</tr>";

while($row = mysql_fetch_array($result))
    {
    echo "<tr>";
    echo "<td>" . $row['intProductID'] . "</td>";
    echo "<td>" . $row['txtProductBowlCode'] . "</td>";
    echo "<td>" . $row['txtProductPrimarySpecies'] . "</td>";
    echo "</tr>";
    }
echo "</table>";
echo "done";

mysql_close($con);
?>

This worked, and I manually changed the $species value and ran it for each of the species of wood in the database...since this was a one time shot it made more sense not to use a list and loop through it - I was bound to miss one or two species anyway.

Answer 1

Shouldn't the set without % come before where with %. Also I think your parameter should be wrapped with a quote as it is string type.

 mysql_query("UPDATE tblsplintersbowlinventory 
                SET txtProductPrimarySpecies='$species' 
                WHERE txtProductBowlCode LIKE 'CONCAT($species, '%')'");