Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    Regular Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    852
    Thanks
    173
    Thanked 94 Times in 94 Posts

    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.)
    Code:
    <?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
    Last edited by low tech; 08-20-2010 at 04:52 AM.

  • #2
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    Well you had it close in your psuedo code.
    PHP Code:
    $SQL "SELECT word, pro, wt, ex WHERE fcl = 1 and (lang = 'en' OR lang = 'cn')"
    Try that.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #3
    Regular Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    852
    Thanks
    173
    Thanked 94 Times in 94 Posts
    Hi

    _Aerospace_Eng_
    Supreme Master coder!

    Thank you very much

    I will try that and post back

    magic

    LT
    Last edited by low tech; 08-20-2010 at 06:06 AM.

  • #4
    Regular Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    852
    Thanks
    173
    Thanked 94 Times in 94 Posts
    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
    Code:
    $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

  • #5
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    I see that you just copy and pasted what I gave you. In the query I wrote I didn't select a table so
    PHP Code:
    $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...
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • Users who have thanked _Aerospace_Eng_ for this post:

    low tech (08-20-2010)

  • #6
    Regular Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    852
    Thanks
    173
    Thanked 94 Times in 94 Posts
    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
    Code:
    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
    Last edited by low tech; 08-20-2010 at 06:49 AM.

  • #7
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #8
    Regular Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    852
    Thanks
    173
    Thanked 94 Times in 94 Posts
    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
    Php array into a javascript array

    and i've seen this (quote: $drire is the PHP array)
    Code:
    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)
    Code:
    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)

    Code:
    $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.
    Code:
    {"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
    Last edited by low tech; 08-20-2010 at 07:59 AM.

  • #9
    Regular Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    852
    Thanks
    173
    Thanked 94 Times in 94 Posts
    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.

    Code:
    {"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

    Code:
    $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
    Last edited by low tech; 08-20-2010 at 10:43 AM.

  • #10
    New Coder
    Join Date
    Aug 2010
    Location
    India
    Posts
    42
    Thanks
    0
    Thanked 5 Times in 5 Posts
    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>";

  • Users who have thanked PradeepKr for this post:

    low tech (08-21-2010)

  • #11
    Regular Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    852
    Thanks
    173
    Thanked 94 Times in 94 Posts
    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

  • #12
    Regular Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    852
    Thanks
    173
    Thanked 94 Times in 94 Posts
    [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.

    Code:
    <?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
    Last edited by low tech; 08-21-2010 at 05:30 AM.

  • #13
    New Coder
    Join Date
    Aug 2010
    Location
    India
    Posts
    42
    Thanks
    0
    Thanked 5 Times in 5 Posts
    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).
    Last edited by PradeepKr; 08-29-2010 at 08:05 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •