PHP Connecting to MSAccess 2010 db.

[TheGoodVibe]

Ok, so I have this connection as a .php file:

<?php
    echo ("Currently connecting to DB");
    //Connect to DB
    $conn=odbc_connect('wordgame.accdb','','') || die ( "Error Connecting to the database");

    //List of all words
    $sql1 = "SELECT * FROM words";
    $rs1= odbc=exec($conn,$sql1);

    //List of all non-allowed words
    $sql2 = "SELECT * FROM cursewords";
    $rs2= odbc=exec($conn,$sql2);

    //List of all Possible words to be added
    $sql3 = "SELECT * FROM tblpossibleWords";
    $rs3= odbc=exec($conn,$sql3);
?>

then I try to use it on my main file:

<?php

echo "starting program";

require ("dbconn.php");
$status = dbconn.php;

$mainWord = odbc_result($rs1,rand(0,odbc_num_rows($rs1)));
$scrambled = str_shuffle($mainWord);

echo ("There are currently ". odbc_num_rows($rs1). " Words in the game\n");
?>

But as far as its going, it only echoes that first line on my main program and nothing else. Im kid of new to PHP so i can't figure out whats wrong with it

 

[jpadie]

<?php
    echo ("Currently connecting to DB");
    //Connect to DB
    $conn=odbc_connect('wordgame.accdb','','') || die ( "Error Connecting to the database");

    //List of all words
    $sql1 = "SELECT * FROM words";
    $rs1= odbc[red]_[/red]exec($conn,$sql1);

    //List of all non-allowed words
    $sql2 = "SELECT * FROM cursewords";
    $rs2= odbc[red]_[/red]exec($conn,$sql2);

    //List of all Possible words to be added
    $sql3 = "SELECT * FROM tblpossibleWords";
    $rs3= odbc[red]_[/red]exec($conn,$sql3);
?>

 

[TheGoodVibe]

This is the advantage of having a different pair of eyes. I can't believe that i missed that.

Now, second questions. I have changed the conn file, but For some reason i still cant get the echo command to display the total number of words.

Please help.

Thanks

 

[jpadie]

i suspect that the driver will not give you the number of rows in the recordset because it will not have retrieved the recordset, just a pointer to the recordset.

to get the number of rows in a recordset the 'normal' method would be this

$result = odbc_exec($conn, "Select count(*)  from tablename");
$count = odbc_fetch_into($result $row) ? trim($row[0]) : 0;

btw, this line

$status = dbconn.php;

does not appear to do anything but will quite probably through at least an E_NOTICE.

you will also have to change the randomising method as it does not know the row count at that point. an alternative method might be this.

$result = odbc_exec($conn, "Select top 1 * from words order by rnd(id)");
$randomWord = odbc_fetch_into($result, $row) ? $row[0] : 'ERROR';

 

[TheGoodVibe]

Ok, I understand what you are doing. The only part that is confusing me me is the last part you showed me.

$result = odbc_exec($conn, "Select top 1 * from words order by rnd(id)");
$randomWord = odbc_fetch_into($result, $row) ? $row[0] : 'ERROR';
[code]

Where is the variable $row coming from?

 

[jpadie]

the variable $row comes from the odbc_fetch_into() function. the id reference in the previous line is the name of the auto_increment column in the table (or at least the numeric pk).

 

[TheGoodVibe]

Ok. Sorry to have wasted your time, but apparently the issue became that the server Im developing on is a Unix server that does not support MS Access.

Ive transfered the data to a MySQL db and it works well for the connect.

Now i'm having different issues.

Here is the finished program i Have:

<?php
$word;
$scrambled;
$me = $_SERVER['PHP_SELF'];
echo "starting program";
$correct = "";
$try = false;

require ("dbconn.php");

if (isset($_REQUEST['userChoice'])) {
  $try = check_word($_REQUEST['userChoice']);
}
else {
  changeword();
}

print_form();

function changeword(){
  global $word;
  global $scrambled;
  $word = mysql_query("Select top 1 * from CurrentWords order by rnd(words)");
  $scrambled = str_shuffle($word);
}

function check_word as bool($guessword){
    global $word
    if($word == $guessword){
        return true;
    } else {
        return false;
    }
}

if ($try){
  $previous = $word;
  changeword();
  $correct = "Your Previous guess of ". $_REQUEST['userChoice']. " Was Correct For the word ". $previous;
}

print_form();

function print_form(){
  $echostring ='<html>
    <head>
    </head>
    <body>
        <form name="wordguess" action="<?php echo $me ?>" method="POST">>
        <table>
            <tbody>
                <tr>
                Try to guess what the following word is: <?php echo $scrambled; ?> <br/>
                </tr>
                 <tr>
                Your Guess: <input type="text" id="userChoice" size="20" /> <br/>
                </tr>
                <tr>
                <td>
                    <input type="submit" name="submit" id="submit" value="Submit">
                </td>
                <td>
                    <input type="submit" name="Newword" id="newWord" value="New Word" onclick="changeword.php">
                </td>
                </tr>
                <tr>
                   <?php echo $correct; ?>
                </tr>
            </tbody>   
        </table>
        </form>
    </body>
</html>';
echo $echostring;
}
?>

When i try to load the page, nothing displays. I would imagine that the first echo would at least show, but it does not. Anyone help me with this please.

Thanks

 

[jpadie]

linux supports ms access just fine.

you need the right odbc drivers of course. they are easily available.

the sql I posted is specific to MS Access. If you are using mysql then different syntax is needed. off the top of my head

Select  * from CurrentWords order by rnd(words) limit 1

function check_word as bool($guessword){
    global $word
    return strtolower($word) == strtolower($guessword) ;
}

have not had time to check the rest.

 

[TheGoodVibe]

Ok. So the program now displays everything, which is good.
Now my next issue, which hopefully will be easy.

I used the following line of code to pull my word from the db:

$word = mysql_query("Select  * from CurrentWords order by rnd(words) limit 1");

I can tell its not working, but I am trying to figure out a better way to do so and i really havent come up with one.

 

[jpadie]

Assuming you have a table with an auto increment pk called id

$sql = <<SQL
SELECT * 
FROM currentwords
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM currentwords ) 
ORDER BY id 
LIMIT 1
SQL;
$r = mysql_query($sql);
$row = mysql_fetch_object($r);
print_r ($row);

 

[jpadie]

you could also just do

select * from table order by rand() limit 1

however this is a very slow query (due to the need for internal temp tables) and should be avoided.

the query in the above post is markedly better but i'm sure that there are ways to optimise it.