mysql proper syntax for where on php variable

[sqlnoob]

$telresult = mysql_query("SELECT COUNT(`word`) FROM `hallofryslan_rhymewords` WHERE `word ='".$rymwurd."'");
$telrow = mysql_fetch_array($telresult);
$telwurd = $telrow['COUNT(word)'];

echo "<P>Aanwezig: ".$telwurd."</P>";`

 

hi I've been trying for ages to find the proper syntax. Somehow this doesn't work. It counts 0 when I know the word exists in the table.

 

Just how do I select a php variable?

 

The variable is a word!

[Pikachu2000]

Ypu're missing a closing ` backtick after `word, and you have no logic in place to check for errors.

[sqlnoob]

Ypu're missing a closing ` backtick after `word, and you have no in place logic to check for errors.

 

still doesn't work with closing `

[sqlnoob]

gah i just want to fetch the damn row and it won't work

[sqlnoob]

$telresult = mysql_query("SELECT COUNT(`word`) FROM `hallofryslan_rhymewords` WHERE `word` ='".$rymwurd."'");
$telrow = mysql_fetch_array($telresult);
$telwurd = $telrow['COUNT(word)'];

 

still doesn't work

[Pikachu2000]

You have the query string embedded in the query execution function, which prevents you from echoing it for debugging, and you aren't checking to see if the query even succeeds.

 

$query = "SELECT field FROM table";
if( $result = mysql_query($query) ) {
     // query succeeded, process results
} else {
     // query failed
     echo "<br>Query $query<br>Produced error: " . mysql_error() . '<br>';
}

[sqlnoob]

$telresult = mysql_query("SELECT COUNT(`word`) FROM `hallofryslan_rhymewords` WHERE `word` ='".$rymwurd."'");
$telrow = mysql_fetch_array($telresult);
$telwurd = $telrow[COUNT(word)];

 

another failed attempt

 

HOW?

[sqlnoob]

$query = ("SELECT COUNT(`word`) FROM `hallofryslan_rhymewords` WHERE `word` ='".$rymwurd."'");
if( $telresult = mysql_query($query) ) {
$telrow = mysql_fetch_array($telresult);
$telwurd = $telrow[COUNT(word)];
echo "<P>Aanwezig: ".$telwurd."</P>";
}
else {
echo "<br>Query $query<br>Produced error: " . mysql_error() . '<br>';}

 

nope still doesn't work

 

sigh

[sqlnoob]

$query = "SELECT COUNT(`word`) FROM `hallofryslan_rhymewords` WHERE `word` ='".$rymwurd."'";
if( $telresult = mysql_query($query) ) {
$telrow = mysql_fetch_array($telresult);
$telwurd = $telrow[COUNT(word)];
echo "<P>Aanwezig: ".$telwurd."</P>";
}
else {
echo "<br>Query $query<br>Produced error: " . mysql_error() . '<br>';}

 

again doesn't work

[Pikachu2000]

Echo the query string, paste it into phpMyAdmin, or whatever you use, and see if it returns the expected results. Beyond that, you probably aren't going to get much help until you can provide a better description of the problem than "it doesn't work".

[sqlnoob]

echoing won't help either

 

OK here is a description of the problem:

 

I have a table with 416000 words!

 

The user posts a word. This word is what I want to count. (I want to check if it exists in the table)

 

I test it with Adam (I know this word is in the table), yet the result is 0, but it should be 1.

 

 

[sqlnoob]

in phpmyadmin:

 

SELECT * FROM `hallofryslan_rhymewords` WHERE word ="Adam"

 

empty result (would be 0 with count)

 

SELECT * FROM `hallofryslan_rhymewords` WHERE word = Adam

 

error 1054 Unknown column 'Adam' in 'where clause'

 

SELECT * FROM `hallofryslan_rhymewords` WHERE word = 'Adam'

 

empty result (would be 0 with count)

 

SELECT * FROM `hallofryslan_rhymewords` WHERE id =31

 

result is id word 31 Adam

 

(this list one is to show that the row does exist)

[mikosiko]

in phpmyadmin:

....

 

SELECT * FROM `hallofryslan_rhymewords` WHERE word = 'Adam'

SELECT * FROM `hallofryslan_rhymewords` WHERE id =31

 

result is id word 31 Adam

 

(this list one is to show that the row does exist)

 

if those 2 results are different then obviously your field `word` contains something different to 'Adam'... most likely a leading space or hidden character. You can try to trim blank spaces or use LIKE  ... as in

WHERE word LIKE '%Adam%'

 

adding to what Picachu already told you... you are no controlling errors .. and yes echo helps to debug your code.... add this  lines at the beginning of your  php file (after <?php)

   // Define how to display/report errors
   ini_set("display_errors", "1");
   error_reporting(E_ALL);

 

it should reveal that you have also an error in your code.... specifically in this line

$telwurd = $telrow[COUNT(word)];

 

[sqlnoob]

that still will get me nowhere ^

 

remember the fetch has to be done on a php variable, not a mere LIKE alone

 

I need the proper syntax for this: SELECT COUNT(word) FROM `hallofryslan_rhymewords` WHERE word ='$externalvariable'

 

 

 

but anyhow thanks for making it apparent to me that the words in the tablecolumn word may be corrupted with spaces

 

that may possible be at least 1 step in the good direction to solving this highly frustrating problem

 

 

 

(damn just what I needed, spaces behind the words, this is may turn out to be one very crappy database

(shouts in frustration, what more surprises will I get next, /me is most displeased))

[mikosiko]

that still will get me nowhere ^

 

remember the fetch has to be done on a php variable, not a mere LIKE alone

...

 

then you are not reading nor thinking in the solutions/suggestions offered to you, that indeed will solve your problem if you take the time to test and adjust them... trim or use LIKE with a variable is 101 coding. We can't see your data, but we already told you what you have wrong in your code (and I offer also a guessing for your wrong data)... the rest is on you.

[sqlnoob]

I appreciate the fact that you're trying to help, but I really need to know what the proper way is to write the WHERE clause and echo the result.

 

For instance, why is this wrong:

 

$telresult = mysql_query("SELECT * FROM `hallofryslan_rhymewords` WHERE `word` ='$someword'");
$telrow = mysql_fetch_array($telresult);
$telwurd = $telrow['word'];

 

And how do I write that the correct way?

 

 

Also what I meant with "that will get me nowhere" is because the LIKE causes difficulties in the later part of the script (it's really complicated bit that searches for words that rhyme) and remember there is like 416000 of those words (now I know that's a Gordian knot and I have solution for that). But I still need to know what the proper way is. Just one good example with a php variable would do.

[fenway]

You've been told to use LIKE instead of =.  REGEXP might work, too.

[mikosiko]

I appreciate the fact that you're trying to help, but I really need to know what the proper way is to write the WHERE clause and echo the result.

 

For instance, why is this wrong:

 

$telresult = mysql_query("SELECT * FROM `hallofryslan_rhymewords` WHERE `word` ='$someword'");
$telrow = mysql_fetch_array($telresult);
$telwurd = $telrow['word'];

 

And how do I write that the correct way?

 

 

is nothing syntactically wrong with that sentence... is incomplete (no error control) but no wrong, could cause problems depending on your objectives.. yes... but that is a different topic.

the first thing you have to do is fix your data inconsistency (or adjust your select to deal with it) and secondly stick with one version of your code.. otherwise is difficult for anybody try to help you.

 

look for the suggestions that has been offered by Pica and other posts and you will be fine ... fenway suggestion to use REGEXP instead of LIKE is good too.

 

this code is the best one that you can continue working on :

$query = ("SELECT COUNT(`word`) FROM `hallofryslan_rhymewords` WHERE `word` ='".$rymwurd."'");
if( $telresult = mysql_query($query) ) {
   $telrow = mysql_fetch_array($telresult);
   $telwurd = $telrow[COUNT(word)];   // YOU MUST FIX THIS LINE... COUNT(word) <> COUNT(`word`)... you can also use an alias instead
   echo "<P>Aanwezig: ".$telwurd."</P>";
} else {
   echo "<br>Query $query<br>Produced error: " . mysql_error() . '<br>';
}

[fenway]

Using an alias would make that pesky COUNT() problem go away.

[sqlnoob]

is nothing syntactically wrong with that sentence... is incomplete (no error control) but no wrong, could cause problems depending on your objectives.. yes... but that is a different topic.

 

thanks! That's what I wanted to know. So the only other obvious answer is that the database table must be corrupted

[sqlnoob]

Using an alias would make that pesky COUNT() problem go away.

 

yeah it doesn't fetch an alias either, gives as much problems as no alias (I know it sounds silly)

 

Just for clarity sake I initially wrote it with alias, when that didn't work I tried without it.

 

[rant]

I'm beginning to think there is something seriously wrong or weird with this webhost.

 

Just out of curiosity, is it at all common to see the sql file when you upload with ftp? By the way never had this much difficulty obtaining the password and url to the phpadmin and I've got experience with like 5 other hosts, none of which are as tedious or difficult as this one.

[/rant]

[sqlnoob]

You've been told to use LIKE instead of =.  REGEXP might work, too.

 

might try that one too, see if it works

 

and if it doesn't work I'm going to recommend another webhost instead of this crappy and frustrating ****

[Pikachu2000]

Instead of changing hosts, you should start listening to the people that are trying to help you instead of just responding with "that won't work" when you obviously haven't tried anything that was suggested. Since you seem to already know everything that won't work, it should be no problem for you to deduce what will work and fix it yourself.

[fenway]

I highly doubt that the webhost is to blame -- or that the database is corrupt.