help with SELECT

Hi

Could some kind person please help me to write this SELECT. I have read what feels like every tutorial. I have been trying to do this for 10 days or more. Now I'm sure I need help.

I have this table with two languages. English (en) and Chinese(cn)

EXAMPLE table
--------------------------------------------
word | pro | wt | ex | wn | wl | fcl | lang | cat | prouk

cat---cat-----n----a cat----1---1----1-----en null null
mao--mao---n----a mao---1---1----1-----cn null null
cat---house--n---a house--2--1----1-----en null null
mao--fangzi--n----a fangzi--2--1----1-----cn null null


I need to select the words based on fcl (flashcard level) and lang (language)
eg I need both selected together

fcl =1 and lang = en AND fcl =1 and lang =cn

BUT I only need the first four fields of each lang---
word = word
pro = pronunciation
wt = wordtype
ex = example

hopefully this would give me an array looking like
english word A, pro, wt, ex
Chinese word A
english word B
Chinese word B
etc etc

which I then hope to pass to Javascript

This is what I have tried (also tried other stuff that didn't work.)
<?PHP
$user_name = "root";
$password = "";
$database = "flashcards";
$server = "127.0.0.1";
$db_handle = mysql_connect($server, $user_name, $password);
if(! $db_handle )
{
die('Could not connect: ' . mysql_error());
}
$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {

$SQL = "SELECT * FROM flashwords
WHERE fclevel='1' && language='en'"; // this gets me ONE side only
$result = mysql_query($SQL);
if(! $result )
{
die('Could not get data: ' . mysql_error());
}

while ($db_field = mysql_fetch_assoc($result)) {

// I TRIED OTHER CODE STUFF HERE -- DIDN'T WORK

print_r(array_values($db_field)); // I CAN SEE THE ARRAY -- I NEED TO PASS IT TO JS

}
mysql_close($db_handle);
}
else {
print "Database NOT Found ";
mysql_free_result($result);
mysql_close($db_handle);
}

?>

I really apprciate any and all help offered,
all opinions welcomed positively

LT

Well you had it close in your psuedo code.
$SQL = "SELECT word, pro, wt, ex WHERE fcl = 1 and (lang = 'en' OR lang = 'cn')";
Try that.

Hi

_Aerospace_Eng_
Supreme Master coder!

Thank you very much

I will try that and post back

magic

LT

Hi
Aerospace_Eng_
Supreme Master coder!


I got this error
how do I find out which verion of mysql server I have? It came with xamp (recent download)

Could not get data: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE fcl = 1 and (lang = 'en' OR lang = 'cn')' at line 1

$SQL = "SELECT word, pro, wt, ex WHERE fcl = 1 and (lang = 'en' OR lang = 'cn')";

$result = mysql_query($SQL);
if(! $result )
{
die('Could not get data: ' . mysql_error());
}

while ($db_field = mysql_fetch_assoc($result)) {




print_r(array_values($db_field));

}


mysql_close($db_handle);

}
else {
print "Database NOT Found ";
mysql_free_result($result);
mysql_close($db_handle);
}

?>

LT

I see that you just copy and pasted what I gave you. In the query I wrote I didn't select a table so
$SQL = "SELECT word, pro, wt, ex FROM flashwords WHERE fcl = 1 and (lang = 'en' OR lang = 'cn')";
should work. Not really sure why this took you 10 days to do though...

Hi
Aerospace_Eng_
Supreme Master coder!

That did the trick ---- I did enter the table name --- but I placed it wrongly.

Why 10 days+?
well I'm new to database, PHP and I like to try to do what I can until I hit a wall:-)

Now i just have to somehow pass this array over to javascript


test code only
Array
(
[0] => import
[1] => [imp&#x254;:rt]
[2] => v
[3] => we import cars
)
Array
(
[0] => 進口
[1] =>
[2] => v
[3] => example
)
Array
(
[0] => provide
[1] => [pr&#x259;v&#x61;id]
[2] => v
[3] => we provide cars
)
Array
(
[0] => 提供
[1] =>
[2] => v
[3] => example
)
Array
(
[0] => suitable
[1] => [su:t&#x259;bl]
[2] => adj
[3] => this is not suitable
)
Array
(
[0] => 合適
[1] =>
[2] => adj
[3] => example
)

LT

This might help you.

http://www.devshed.com/c/a/MySQL/Loading-JavaScript-Arrays-with-MySQL-Data/

HI
Aerospace_Eng_
Supreme Master coder!

json encode?

first:
Thank you for that link---- I've actually got that bookmarked and have read it several times --- but I htink it creates a js array from PHP --- I want to get my PHP array into my js page.

I read that using json is the way to go, one eg
http://codingforums.com/showthread.php?t=103320

and i've seen this (quote: $drire is the PHP array)
print ( "<script type='text/javascript'>var drire=" . json_encode('$drire') . ";</script>" );

I was hoping my array would look SOMETHING like this (example from Old Pedant)
var words = [
[ new eWord("Tom","tahm","Tom isn't too clever"),
new cWord("...","...","...") ],
[ new eWord("boot", .... ),
new cWord(...) ],
etc.
];

I TRIED this json (select words changed for test database)

$SQL = "SELECT word, prouk, wordtype, example FROM flashwords WHERE fclevel = 1 and (language = 'en' OR language = 'cn')";

$result = mysql_query($SQL);
if(! $result )
{
die('Could not get data: ' . mysql_error());
}
while ($db_field = mysql_fetch_assoc($result)) {
echo json_encode($db_field);

BUT it (json) turned out field names and values like so.
{"word":"import","prouk":"[impɔ:rt]","wordtype":"v","example":"we import cars"}etc etc

I don't really know how this all works yet.

and the days add up:-)

LT
ps if I sound a little confised, it's because I am

HI

I used json encode and now I have this PHP array -(db_field?)-- (not sure IF I need to use json by the way)

How do I pass it (this array) to javascript from php? I can't work it out --- been trying for ages now.

{"word":"import","prouk":"[impɔ:rt]","wordtype":"v","example":"we import cars"}
{"word":"\u9032\u53e3","prouk":null,"wordtype":"v","example":"example"}
{"word":"provide","prouk":"[prəvaid]","wordtype":"v","example":"we provide cars"}
{"word":"\u63d0\u4f9b","prouk":null,"wordtype":"v","example":"example"}
{"word":"suitable","prouk":"[su:təbl]","wordtype":"adj","example":"this is not suitable"}
{"word":"\u5408\u9069","prouk":null,"wordtype":"adj","example":"example"}


My PHP

$SQL = "SELECT word, prouk, wordtype, example FROM flashwords WHERE fclevel = 1 and (language =

'en' OR language = 'cn')";

$result = mysql_query($SQL);
if(! $result )
{
die('Could not get data: ' . mysql_error());
}

while ($db_field = mysql_fetch_assoc($result)) {

echo json_encode($db_field). "<br />"; // I think db_fieldd is name of array right?

}

LT

PHP gave you array which you need to pass to javascript. How come json came into picture?

Try directly passing variable to javascript.

$SQL = "SELECT word, prouk, wordtype, example FROM flashwords WHERE fclevel = 1 and (language = 'en' OR language = 'cn')";

$result = mysql_query($SQL);
if(! $result )
{
die('Could not get data: ' . mysql_error());
}
echo "<script>";
while ($db_field = mysql_fetch_assoc($result))
{
echo "var en_word = '",$db_field['word'],"'";
echo "var en_prouk = '",$db_field['prouk'],"'";
}
echo "alert('en_word ='+en_word)";
echo "</script>";

Hi
PradeepKr

Thank you for the code --- haven't had a chance to try it yet but will post back as soon as.


how come json came into picture?

I read several articles that mentioned the use of json when getting array from php to javascript --- so I tried to see ---

LT

[edit: PLease do not continue this thread. I need to have a rethink and will post new question in new thread. Thank you for your efforts --- very helpful]


Hi PradeepKr (as a note, this is all the page minus the HTML tags and some open close code.)

I tried this exactly as is ---- but I get (js) error expected ; ----- however it ref the first line <?PHP so that can't be right.

I can't find any fault in the script you offered tho the browser is obviously expecting smthg somewhere:-)

I didn't get the alert('en_word ='+en_word)"; either ---- which I guess is right if have error.

<?PHP
[code to open database]

if ($db_found) {

$SQL = "SELECT word, prouk, wordtype, example FROM flashwords WHERE fclevel = 1 and (language = 'en' OR language = 'cn')";

$result = mysql_query($SQL);
if(! $result )
{
die('Could not get data: ' . mysql_error());
}
echo "<script>";
while ($db_field = mysql_fetch_assoc($result))
{
echo "var en_word = '",$db_field['word'],"'";
echo "var en_prouk = '",$db_field['prouk'],"'";
}
echo "alert('en_word ='+en_word)";
echo "</script>";

mysql_close($db_handle);

}

[code to free result etc]
?>

LT
thanks for the effort, much appreciated

1. Run it in Mozilla and then go to Tools (Menu Bar of mozilla)-> Error Console. Check the last entries (which should be related to your webpage).

2. Copy paste the "View source" of the HTML here (so that we can have a look).