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.
Last edited by bradgrafelman; 01-27-2013 at 05:33 PM.
---------------
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 $addslashes() where you meant to type addslashes().
----------------
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?
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.
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.
Last edited by Weedpacket; 01-28-2013 at 11:58 PM.
You're welcome.
You would be well advised to also consider the warning against the entire mysql extension.
Originally Posted by timstring
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:
Code:
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.
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.
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.
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.
Originally Posted by timstring
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.
Also, from your question above, there is no CellNum column in the CallCount table. I would assume that you're comparing to the CellPhone 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.: 1CallCount.id vs. 1WorkOrders.IdNum). Likewise with pluralization (CourtneyTechs [plural] vs. 1CallCount [singular]), [seemingly] arbitrary numbers in the table names, and downright "odd" column names (1WorkerOrders_Id - huh?).)
Originally Posted by timstring
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.
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
Code:
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:
Code:
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?
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").
The column definition need to be able to store exactly the same information. Hence needing the same size, signed, type, etc.
Sadly, nobody codes for anyone on this forum. People taste your dishes and tell you what is missing, but they don't cook for you. ~anoopmail I'd rather be a comma, then a full stop. User Authentication in PHP with MySQLi - Don't forget to mark threads resolved - MySQL(i) warning
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").
Why would a counter fail to advance
Reply With Quote
Originally Posted by timstring
Bookmarks