Page 1 of 2
can't make it work
Posted: Wed Dec 10, 2003 10:58 am
by lisawebs
2 weeks and nothing!
I've changed the code several times
and is not working at all!,
I've tried the "" , and the ''
around field_names and variables,
and it doesn't work...
http://www.usa21.net/lisawebs_demo.php
Posted: Wed Dec 10, 2003 11:10 am
by microthick
So it's something with the query that's wrong, right?
I see that there are some variables that you should have quoted.
For example:
This is what you have:
Code: Select all
$sql = "UPDATE sia_test SET rec_date = $rec_date , rec_unit = $rec_unit, rec_code = $rec-code, rec_desc = $rec_desc WHERE rec_date = $rec_date AND rec_unit = $rec_unit AND rec_code = $rec_code";
1) Should it be $rec_code and not $rec-code as it says right now?
2) If rec_code is a varchar field, you should single quote '$rec_code'
3) You should single quote '$rec_desc'
4) Your where clause is written incorrectly.
This is what I feel it should read as:
Code: Select all
$sql = "UPDATE sia_test SET rec_date = $rec_date , rec_unit = $rec_unit, rec_code = '$rec_code', rec_desc = '$rec_desc' WHERE rec_date = $rec_date AND rec_unit = $rec_unit AND rec_code = '$rec_code'";
(Assuming $rec_unit is an INT, $rec_date is DATETIME, $rec_code is VARCHAR.)
Wait, wait, wait. I think there are logic errors with this UPDATE.
What is your key for this table? Your where clause should just contain a reference to the primary key of this table.
Currently, it's looking for a table that matches $rec_date, $rec_unit, $rec_code and just updates the description. Is that what you intended this update should do?
Posted: Wed Dec 10, 2003 11:18 am
by microthick
Wait again, just noticed your table declaration on your website.
Code: Select all
$sql = "UPDATE sia_test SET rec_date = '$rec_date', rec_unit = '$rec_unit', rec_code = '$rec_code', rec_desc = '$rec_desc' WHERE rec_id = $rec_id ";
But to do it this way would mean to re-work some of your presentation code. You would need to know the $rec_id before the update statement, either by passing it into the function, or through some other means.
my god!
Posted: Wed Dec 10, 2003 4:58 pm
by lisawebs
Thanks
so every char variable must be enclosed (''), the rest types don't, right?
(I didn't find this in the huge manual)
I was told that if no index is specified on sql, the databse
will automatically improve the search using the indexes available,
but ANYWAY the primary key must be define on WHERE... clause?
What I intend to do is:
allow users to search 1 record, with the known (comprehensive) data
as date, unit, code..., but the primary key is rec_id (autoincrement)
so the WHERE ... doesn't work the same in SELECT and other commands?
Posted: Wed Dec 10, 2003 5:03 pm
by microthick
Hi Lisa,
You don't NEED to use the primary key in your WHERE clause, but it's generally how updates are performed.
If $rec_code, $rec_unit, and $rec_date combined are another key, then you can use those like you are.
But then, in your code (or in your table schema) you have to make sure that (rec_code, rec_unit, rec_date) is a key. Cause otherwise, imagine if this happens. Imagine you insert a record where rec_code = '1', rec_unit='1', rec_date='1' and rec_desc='1'. Then you insert another record with rec_code = '1', rec_unit='1', rec_date='1' and rec_desc='2'. The only thing that is different is the rec_desc and this immediately implies that (rec_code, rec_unit, rec_date) is no longer a key.
INDEXes are not the same as keys, if I'm correct. Indexes are things that the database can use to speed up searching many, many records. Keys distinctly identify the different records.
Regarding quotes, yes, you need to wrap your character strings with quotes. Integers and date types don't need to be wrapped.
And WHERE clauses work exactly the same in UPDATE statements as they do in SELECT statements.
changed but doesn't work
Posted: Thu Dec 11, 2003 7:10 am
by lisawebs
Thanks again,
I changed the fmyread() function,
quotes and $rec_desc = $rows[$rec_desc].
then, to understand what's going on
passed the value of $rows to Jscript
showing (alert) the value.
well,
the msg "record not found" doesn't appear
even with unexisting records
$rec_desc doesn't come up (even defined as global)
and the $rows content appear as 'array'
suggestions?
http://www.usa21.net/lisawebs_demo.php
almost done!
Posted: Thu Dec 11, 2003 7:24 am
by lisawebs
now I can write, update and delete,
but read and "show all" are still with problems
why the different quotes
Posted: Thu Dec 11, 2003 7:26 am
by lisawebs
why the field names required a different quote, ?
actually an acent (`fieldname`)
Posted: Thu Dec 11, 2003 9:33 am
by microthick
In function fmyread($opthis):
The line
rec_desc = $rows[$rec_desc];
should actualy look like:
rec_desc = $rows["rec_desc"];
In function fmyselect($opthis):
You are repeating this line twice:
$rows = mysql_fetch_array($result);
I don't think this would cause problems, but you should remove the first instance if that line.
And your echo line should look like:
echo("t[$i] = '".$rows["rec_date"]." - ".$rows["rec_desc"]."\n';");
Posted: Thu Dec 11, 2003 9:33 am
by microthick
No clue why it'd need an accent.
docs about quotes
Posted: Thu Dec 11, 2003 9:54 pm
by lisawebs
I could find documentation
explaining clearly the use of "" / '' / ``
not even in mysql manuals...
Posted: Thu Dec 11, 2003 10:09 pm
by microthick
In PHP, this is how it works.
For "", any variables within "" will be interpretted. So:
$something = "nothing";
echo "this is $something";
will print out this is nothing
For '', any variables within '' will not be interpretted. So:
$something = "nothing";
echo 'this is $something';
will print out this is $something
\ cancels out the next ' or " so that they're more like grammar punctuations, rather than php syntax constructs.
When you start combining SQL and PHP syntax or PHP and JavaScript, it gets more complicated.
YES!!!!! VICTORIOUS!!!!
Posted: Thu Dec 11, 2003 10:24 pm
by lisawebs
Aha!!! now it works!!!
Thank you VERY MUCH,
based on this experience, as soon as I get some experience
I'll dedicate some time to help others,
Forums are great!
Of course, this step means
others more complex will come up soon...
platform differences
Posted: Fri Dec 12, 2003 9:47 am
by lisawebs
I'm running the same code in
http://www.usa21.net/lisawebs-demo.php
(linux platform-online)
and in WIN-XP-localhost, here
the mysql_num_rows($result) introduces a problem
ERRMSG: unable to save result set, referring to the $rows[] loop
I need a safe way (in all platforms) to know
how many lines of result I got (0 or nn)
Are there any reference to cross-platform issues in PHP/mysql?
PS: I saw your websites, like your style
(tip) show more faces!
Posted: Fri Dec 12, 2003 10:00 am
by microthick
My website? More faces?!
I've looked at how you're using mysql_num_rows() and that's exactly how I use it. It works ok for me on both linux and winxp.
I don't know exactly what's wrong.
I will point out a problem you'll face though, and I think I touched upon it on your older thread.
Since you have your date field as a VARCHAR, when you go someString > someOtherString, (or in your case rec_date > '$rec_date'), it will compare the strings to see which one is greater, not which "date" is greater.
To elaborate on this, say in your database rec_date equals 'bbbbb' and $rec_date = 'aaaaa'. $rec_date in this case is less than rec_date. This is the case in other programming languages and I've never even tried to do something like this in MySQL so I don't know if it might be the cause of the error you're having. It might work on linux but not windows.
To do date comparisons, that field needs to be a DATETIME.