Jump to content

mysql proper syntax for where on php variable


sqlnoob

Recommended Posts

$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!

Link to comment
Share on other sites

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>';
}

Link to comment
Share on other sites

$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

Link to comment
Share on other sites

$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

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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)];

 

Link to comment
Share on other sites

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)) :(

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>';
}

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.