Why would a counter fail to advance

[timstring]

This should be a very simple script, but something is terribly wrong, and I don't know what.

<?php



$host="localhost";
$user="user";
$password="pass";
$Dbname = "Food";

$cxn = mysql_connect($host,$user,$password)
or die (mysql_error() . "couldn’t connect to server");


mysql_select_db($Dbname, $cxn)
or die(mysql_error() . "couldn't select database");

$RecipeTitle = addslashes($_POST['RecipeTitle']);

mysql_query("INSERT INTO `Recipes` (`RecipeTitle`) VALUES ('$RecipeTitle')")
or die(mysql_error() . "  You dope, this does not work");

for ($x=1; $x<=20; $x++)
	{
echo $x . '<br />';

	$Qty = 'q' . $x;
	$Mea = 'm' . $x;
	$Ing = 'i' . $x;
	$Quantity = $addslashes($_POST['$Qty']);
	$Measure = $addslashes($_POST['$Mea']);
	$Ingredient = $addslashes($_POST['$Ing']);

echo $x . ' ' . $Qty . ' ' . $Quantity;

	mysql_query("UPDATE `Recipes` SET `$Qty` = '$Quantity', `$Mea` = '$Measure', `$Ing` = '$Ingredient' WHERE RecipeTitle = '$RecipeTitle'") 
		or die(mysql_error() . "  Update failed");


	echo $Quantity .  '  ' . $Measure .  '  ' . $Ingredient . '<br />'; 
}
?>

The first 'echo' inside the 'for' loop returns a '1', and there the script dies. The second and third echoes returns nothing at all, and the update query doesn't update anything. I have to be overlooking something that's painfully obvious.

[traq]

the mysql_*() functions are deprecated. don't use them!

addslashes is even worse.

---

If you'll enable error reporting, you'll get something like the following:

Notice: Undefined variable: addslashes in ...
Fatal error: Function name must be a string in ...

You're typing [font=monospace]$addslashes()[/font] where you meant to type [font=monospace]addslashes()[/font].

---

As for your UPDATEs, what exactly are you trying to do?

Your Recipes table has twenty different q*,m* and i* fields, which all hold the same values?

[timstring]

I said the solution would be painfully obvious.

Thanks for the tip about addslashes.

duh, no, the 20 iterations of q, m, & i all contain distinct values.

What I am trying to accomplish is to avoid an insert query that has 63 values, all surrounded by 'mysql_real_escape_string', when i can cut that down to 3 with a for loop.

[Weedpacket]

I think traq means the same type of values - they all contain the same kind of information (otherwise why call them q1, q2, q3, .... etc.?). Having columns named like that (and queries assembled the way you're doing it) is a very strong sign that your database design is broken. Does every Recipe always have exactly twenty Qty, Ing, and Mea things each? What do you do with recipes that have fewer? Or more?

It looks like you're missing the "relational" from your "relational database". Read up on "First Normal Form" because from what we've seen your database lacks it.

[traq]

timstring;11023299 wrote:

I said the solution would be painfully obvious.

Thanks for the tip about addslashes.

You're welcome.
You would be well advised to also consider the warning against the entire mysql extension.

timstring;11023299 wrote:

duh, no, the 20 iterations of q, m, & i all contain distinct values.

What I am trying to accomplish is to avoid an insert query that has 63 values, all surrounded by 'mysql_real_escape_string', when i can cut that down to 3 with a for loop.

Why? Why would you favor 20 queries over one that does the same job? That's horribly inefficient. Round-trips to the database are one of the most time-consuming parts of a PHP script.

As Weedpacket says, what I was getting at is the simple fact that you have sixty-three columns with mostly identical names in a single table strongly suggests that you need to redesign your tables.

Maybe:

CREATE TABLE `recipe`(
   `id` INT NOT NULL AUTO_INCREMENT 
      COMMENT 'this is your primary key'
  ,`title` VARCHAR(100) NOT NULL 
      COMMENT 'the name of the recipe - 
      if this is UNIQUE it could be a natural key,
      and you wouldnt need the `id` col at all'
  ,PRIMARY KEY( `id` )
);

CREATE TABLE `ingredient`(
   `id` INT NOT NULL AUTO_INCREMENT
  ,`recipe_id` INT NOT NULL
  ,`quantity` INT NOT NULL
  ,`measure` VARCHAR(100) NOT NULL
     COMMENT 'Im assuming this is the unit of measure for `quantity`...?'
  ,`ingredient` VARCHAR(100) NOT NULL
  ,PRIMARY KEY( `id` )
  ,FOREIGN KEY( `recipe_id` ) REFERENCES `recipe`( `id` )
      ON UPDATE CASCADE
      ON DELETE CASCADE
);

-- you're not limited to 20 ingredients, nor do you have empty columns in any rows.
-- all ingredients belong to a recipe.
-- if you delete a recipe, its ingredients are gone as well.

You'd need to use the INNODB engine, rather than the default MyISAM.
Even if you don't use the foreign key, this structure would simplify your database, how you manage it, and also allow for more efficient reading.

[timstring]

I am trying to follow your logic, and I have thee questions for now. I'm sure I'll more to ask later. What is the significance of recipe_id? How is this number generated? Right now, all the rows have values of 0.

Here's the more pressing need: I have three tables that need to relate to one another. Here's the structure:

Table CourtneyTechs is the master file and has TechNum & CellNum among other fields. TechNum is the primary key, but CellNum is also unique.
Table CallCount is one data file with 57,215 records, keyed to IdNum containing CellNum & data fields.
Table WorkOrders is another data file of the same size, keyed to IdNum, and containing TechNum & data fields.

What I need to happen is this: CourtneyTechs says to CallCount: "Here's the CellNum for tech John Smith. Give me all the rows that have the same CellNum. Then, I need CourtneyTechs to say to WorkOrders: "Here's the TechNum for John Smith. Give me all the rows that have the same TechNum, and so forth until the all the rows in CourtneyTechs have been processed.

I was able to create the two tables with the and import the .csv files I have. Now, my 2nd question is: After I created the two tables with Foreign Keys, Navicat says that the name of one foreign keys is "1workorders_ibfk_1", and the name of the 2nd table is equally puzzling. How is that name generated, and what is its significance?

Oops, I need to ask a 4th question: How do I get the data out of the two tables? If I execute a 'SELECT * ' on CourtneyTechs will that automatically pull the data from the two subjugated tables? I would assume so, but asking almost never hurts.

Oh No!! Number 5! If I set up a form to insert data, how would MySQL know to call up another row? Is this automatic, or does some trick with PHP need to be executed?

Are all these questions as clear as mud? The answers will probably be long enough to write a master's thesis, when I'm just a lowly apprentice. Thanks as always for all the help.

tim

[traq]

recipe_id should be the same value as the id of the recipe it belongs to (i.e., if an ingredient belongs to recipe #42, then recipe_id would be be 42). That way, you can join the ingredients with the correct recipe.

---

and yes, your other questions are about as clear as mud. I've got the idea of what you're asking, but before I answer:

1) Could you show me the actual table schemas (SQL code to create the tables)? Much better than trying to infer the structure from a description. (You can use the query "SHOW CREATE TABLE tablename" to get this; just cut-n-paste the output here.)

2) Queries should be fairly straightforward. We'll deal with this after you show me your schemas.

3) I've never used Navicat. I have no idea how it decides things. If it's at all worthwhile, it should allow you to choose (and name) your own foreign keys.

4) No. As I said, this isn't too complicated, but let's see the table structure first.

5) I'm not certain what this question means.

[timstring]

CourtneyTechs:

CREATE TABLE `CourtneyTechs` (
  `System` varchar(30) NOT NULL,
  `TechNum` int(6) NOT NULL,
  `FirstName` varchar(25) NOT NULL,
  `LastName` varchar(25) NOT NULL,
  `CellNum` int(15) NOT NULL,
  `AltCell` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`TechNum`),
  UNIQUE KEY `CourtneyTechs` (`TechNum`) USING BTREE,
  KEY `CellNum` (`CellNum`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Here's the code for 1CallCount:

CREATE TABLE `1CallCount` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `CourtneyTechs_Id` int(6) NOT NULL,
  `CellPhone` int(11) NOT NULL,
  `CallCount` int(11) NOT NULL
  PRIMARY KEY (`id`),
  KEY `1callcount_ibfk_1` (`CourtneyTechs_Id`),
  CONSTRAINT `1callcount_ibfk_1` FOREIGN KEY (`CourtneyTechs_Id`) REFERENCES `CourtneyTechs` (`CellNum`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=57216 DEFAULT CHARSET=latin1

1WorkOrders:

CREATE TABLE `1WorkOrders` (
  `IdNum` int(11) NOT NULL AUTO_INCREMENT,
  `TechNum` int(11) NOT NULL,
  `1WorkerOrders_Id` int(11) NOT NULL,
  `CellNum` int(11) NOT NULL,
  `WorkOrders` int(11) NOT NULL,
  `DI` int(11) DEFAULT NULL,
  `DW` int(11) DEFAULT NULL,
  `IN` int(11) DEFAULT NULL,
  `NP` int(11) DEFAULT NULL,
  `RS` int(11) DEFAULT NULL,
  `SR` int(11) DEFAULT NULL,
  `TC` int(11) DEFAULT NULL,
  `UP` int(11) DEFAULT NULL,
  PRIMARY KEY (`IdNum`),
  KEY `1WorkerOrders_Id` (`1WorkerOrders_Id`),
  CONSTRAINT `1workorders_ibfk_1` FOREIGN KEY (`1WorkerOrders_Id`) REFERENCES `CourtneyTechs` (`TechNum`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=82200 DEFAULT CHARSET=latin1

The more I read through this post, the less I understand.

[traq]

timstring;11023523 wrote:

How do I get the data out of the two tables? If I execute a 'SELECT ' on CourtneyTechs will that automatically pull the data from the two subjugated tables? I would assume so, but asking almost never hurts.

No, and you should be specifying the names of the columns you want. is almost never the right solution.

timstring;11023523 wrote:

What I need to happen is this: CourtneyTechs says to CallCount: "Here's the CellNum for tech John Smith. Give me all the rows that have the same CellNum. Then, I need CourtneyTechs to say to WorkOrders: "Here's the TechNum for John Smith. Give me all the rows that have the same TechNum, and so forth until the all the rows in CourtneyTechs have been processed.

Before answering this, I have other questions:

FOREIGN KEY (`CourtneyTechs_Id`) REFERENCES `CourtneyTechs` (`CellNum`)

Is that a typo? Why would the foreign key "[font=monospace]CourtneyTechs_Id[/font]" reference [font=monospace]CourtneyTechs.Cellnum[/font]?

Likewise,

FOREIGN KEY (`1WorkerOrders_Id`) REFERENCES `CourtneyTechs` (`TechNum`)

Also, from your question above, there is no [font=monospace]CellNum[/font] column in the CallCount table. I would assume that you're comparing to the [font=monospace]CellPhone[/font] column, but you know what they say about assumptions.

I would recommend clearing up these problems before you start trying to create queries. Are you still in the process of designing this, or is it already in production?

(Also, personally, I prefer all lowercase table/column names - they're easier to type, and also to remember. It can get confusing when you have to memorize which letters/words/partsofwords are capitalized, especially when the same rules don't apply to each name (e.g.: [font=monospace]1CallCount.id[/font] vs. [font=monospace]1WorkOrders.IdNum[/font]). Likewise with pluralization (CourtneyTechs [plural] vs. 1CallCount [singular]), [seemingly] arbitrary numbers in the table names, and downright "odd" column names ([font=monospace]1WorkerOrders_Id[/font] - huh?).)

timstring;11023523 wrote:

Oh No!! Number 5! If I set up a form to insert data, how would MySQL know to call up another row? Is this automatic, or does some trick with PHP need to be executed?

I'm still not sure what you're really asking, but no, you need to write deliberate INSERT queries for all the data (for each table) you want to insert. No tricks, just queries.

[timstring]

Try number two:

CREATE TABLE `CourtneyTechs` (
  `SystemNum` int(11) NOT NULL,
  `System` varchar(30) NOT NULL,
  `TechNum` int(6) NOT NULL,
  `FirstName` varchar(25) NOT NULL,
  `LastName` varchar(25) NOT NULL,
  `CellNum` int(15) NOT NULL,
  `AltCell` int(11) DEFAULT NULL,
  PRIMARY KEY (`TechNum`),
  UNIQUE KEY `CourtneyTechs` (`TechNum`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `WorkOrders`(
   `IdNum` INT NOT NULL AUTO_INCREMENT
  ,`CourtneyTechs_IdNum` INT NOT NULL
  ,`TechNum` INT NOT NULL
  ,`WorkOrders` INT NOT NULL
  ,PRIMARY KEY( `IdNum` )
  ,FOREIGN KEY( `TechNum` ) REFERENCES `CourtneyTechs`( `TechNum` )
      ON UPDATE CASCADE
      ON DELETE CASCADE

So far as I can tell, this script had the desired results.

This one, however, returns an error:

CREATE TABLE `CallLog`(
   `IdNum` INT NOT NULL AUTO_INCREMENT
  ,`CourtneyTechs_IdNum` INT NOT NULL
  ,`CellNum` INT NOT NULL
  ,`WorkDate` DATE NOT NULL
  ,`CallCount` INT NOT NULL
  ,PRIMARY KEY( `IdNum` )
  ,FOREIGN KEY( `CellNum` ) REFERENCES `CourtneyTechs`( `CellNum` )
      ON UPDATE CASCADE
      ON DELETE CASCADE
);

I'm getting an error code 150. CellNum and CourtneyTechs.CellNum are both defined as integers. What am I missing?

The more I know, the less I understand

[traq]

timstring;11023561 wrote:

`CourtneyTechs`.`CellNum` int(15) NOT NULL
      `CallLog`.`CellNum` INT NOT NULL

I'm getting an error code 150. CellNum and CourtneyTechs.CellNum are both defined as integers. What am I missing?

They need to be the same size.

[Bonesnap]

traq;11023563 wrote:

They need to be the same size.

Not really pertinent to this situation, but I happened to read last night that they also need to be "signed" the same (either both "signed" or "unsigned").

[Derokorian]

The column definition need to be able to store exactly the same information. Hence needing the same size, signed, type, etc.

[traq]

Bonesnap;11023681 wrote:

Not really pertinent to this situation, but I happened to read last night that they also need to be "signed" the same (either both "signed" or "unsigned").

indeed.