can't make it work

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

lisawebs
Forum Commoner
Posts: 44
Joined: Wed Nov 19, 2003 6:21 pm

can't make it work

Post 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
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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?
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
lisawebs
Forum Commoner
Posts: 44
Joined: Wed Nov 19, 2003 6:21 pm

my god!

Post 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?
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
lisawebs
Forum Commoner
Posts: 44
Joined: Wed Nov 19, 2003 6:21 pm

changed but doesn't work

Post 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
lisawebs
Forum Commoner
Posts: 44
Joined: Wed Nov 19, 2003 6:21 pm

almost done!

Post by lisawebs »

now I can write, update and delete,
but read and "show all" are still with problems
lisawebs
Forum Commoner
Posts: 44
Joined: Wed Nov 19, 2003 6:21 pm

why the different quotes

Post by lisawebs »

why the field names required a different quote, ?
actually an acent (`fieldname`)
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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';");
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

No clue why it'd need an accent.
lisawebs
Forum Commoner
Posts: 44
Joined: Wed Nov 19, 2003 6:21 pm

docs about quotes

Post by lisawebs »

I could find documentation
explaining clearly the use of "" / '' / ``
not even in mysql manuals...
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
lisawebs
Forum Commoner
Posts: 44
Joined: Wed Nov 19, 2003 6:21 pm

YES!!!!! VICTORIOUS!!!!

Post 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...
lisawebs
Forum Commoner
Posts: 44
Joined: Wed Nov 19, 2003 6:21 pm

platform differences

Post 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!
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

My website? More faces?! :P

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.
Post Reply