Help Writing an SQL Query

I need help writing a SQL query for my SQL.

Basically I have a set of Dealers of let's say Flowers in one Table and in another table I have the products they sell.

I need (if it can) the SQL Query to scan the Dealer Table and find all the Dealer names that contain Rose in the name and add that Dealer to the Dealer Category Table with the Rose ID of 39

So if the Dealers Name is Bob's Rose Shop and it's unique ID is 66
It would be selected and added to the Dealer Category Table under Rose

The Dealer Category Table is
iddc int(11) auto_increment
catid int(11)
dealerid int(11)

And in the Example above Bob's Rose Shop would appear as

catid - 5
dealerid - 66

I hope this example explains what I'm trying to do but I'm not sure. Please let me know if you can do this and how much it would cost and how you would like to be paid.

Thanks

 

I will try to help you, but i don`t understand what you want. It would be easier if you show all the tables used and tell exactly what you want to do

 

Your Right I don't think I explained it well at all There is actually three tables involved and I only mentioned two. I had a programmer write this from Romania but I lost his contact when my computer crashed.

The Query needs to scan the dealers table looking for all dealer names from the dealername column that contain the word Rose in the Dealer Name.

Then use the id for Rose from the table categories which in this case is 5

Then add it to the dealercategories table with the catid as 5 and Bob's Rose shop as 66 (Bob's dealerid) - Maybe there is only 2 tables needed since you already know rose's ID? There is about 1500 different dealers I need to run this query for so I suppose it would need some kind of Loop. The Table structure is below and I may be making this harder than it needs to be.

Table: dealers
dealerid - auto_increment
dealername - The Dealer (Bob's Rose Shop)

Table: categories
idcat - auto_increment
categoryname - The Category (Rose)

Table: dealercategories
iddc - auto_increment
Catid - The Category ID (Rose ID)
Dealerid - The Dealer (Bob's Rose Shop ID)

 

that includes php too. i`ll try to make a script, but i`m not sure it works, because i`m not sure i understand
this suposed you are already connected to the db

<?php
$sql = "SELECT * FROM dealers";
$query = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($query)) {
if(eregi("Rose",$row['dealername'])) {
$sql = "SELECT * FROM categories WHERE idcat='".$row['dealerid']."'";
$query = mysql_query($sql) or die(mysql_error());
$row2 = mysql_fetch_assoc($query);
$sql = "INSERT INTO dealercategories(catid,dealerid) VALUES('".$row2['idcat']."','".$row['categoryname']."')";
}
}
?>

PHP:

this should work

 

Sorry for the delay getting back to you -

Where would I put this code in PHPMyAdmin to give a try?

Sorry I'm pretty new at trying to figure this stuff out

If and when it works I need to know your payment information

 

first, please make a backup. i don`t want you to lost or brake the database if it doesn`t work
then, get this code and put the username, password and database
after that make a file, let`s say query.php and upload it to a folder on your host. after that access it from the browser
don`t forget, first make a backup

  
      <?php
      $db_user = "" //database username
      $db_password = "" //database password
      $db_name = "" /database name
      mysql_connect("localhost",$db_user,$db_password) or die(mysql_error());
     mysql_select_db($db_name);
      $sql = "SELECT * FROM dealers";
      $query = mysql_query($sql) or die(mysql_error());
      while($row = mysql_fetch_assoc($query)) {
      if(eregi("Rose",$row['dealername'])) {
      $sql = "SELECT * FROM categories WHERE idcat='".$row['dealerid']."'";
      $query = mysql_query($sql) or die(mysql_error());
      $row2 = mysql_fetch_assoc($query);
      $sql = "INSERT INTO dealercategories(catid,dealerid) VALUES('".$row2['idcat']."','".$row['categoryname']."')";
       }
        }
  .
      ?> 

PHP: