Jump to content

Recommended Posts

Hello all, I'd like to thank thescientist and niche for the help they gave me on my first help topic. I am now here asking in the PHP side of things of how i can get php to connect to a .mdb (MS Access Database), use that to input text, save, and display. Here is my html source:

<html><head><title>Tasks to Complete</title></head><body><table border="1" width="100%" height="100%"><tr width="100%" height="10%">  <td colspan="2" height="10%">   <h2 align="center">Welcome to the Task List</h2>  </td></tr><tr width="100%" height="90%">  <td width="50%" height="100%">   <form action="insert.php" method="post">    <fieldset>	 <legend>Insert Tasks Here:</legend>	 <textarea rows="15" cols="50" name="task"></textarea>	 </br>	 </br>	 <input type="radio" name="importance" id="highly important" value="Highly Important">Highly Important</input>	 </br>	 <input type="radio" name="importance" id="important" value="Important">Important</input>	 </br>	 <input type="radio" name="importance" id="can wait" value="Can Wait">Can Wait</input>	 </br>	 <p>Date Assigned    <input type="text" name="date" id="date" /></p>	 <p>Time Assigned    <input type="text" name="time" id="time" /></p>	 <input type="submit" id="submit task" value="Submit Task" />    </fieldset>   </form>  </td>  <td width="50%" height="100%">     <iframe width="100%" height="100%" src="viewTasks.php">      </iframe>  </td></tr></table></body></html>

I tried to use mysql in php, but it did not work. The three attached files are the php i have created so far in trying to get a mysql database. If it can work, i'll stick with the php files, other wise, i'll need some help in trying to get connected to a .mdb file. Thanks, -ProSpartan

connect.php

insert.php

viewTasks.php

Link to post
Share on other sites

in the insert.php, i get the error on line 29 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given. In the function call is $result which is equal to mysql_query("SELECT * FROM tasksTable"); Also, when submitting a task via submit button, it pulls an error stating that no database is selected, even though the code should be selecting one (found in insert.php). Now the connec.php work fine, as far as i know. It gives me no errors.

Edited by ProSpartan
Link to post
Share on other sites

In all fairness, you never showed us your code for that part of it.http://w3schools.invisionzone.com/index.php?showtopic=45578 Since it seems you fixed your login issue, I don't see why you wouldn't want to continue with MySQL, considering the error you got is very common and has specific cause. Namely, you probably have connected to the database correctly. Take a look at these resources, and consider providing your previous code.http://www.w3schools.com/php/php_mysql_connect.asphttp://w3schools.invisionzone.com/index.php?showtopic=44106http://www.w3schools.com/php/php_mysql_select.asphttp://www.w3schools.com/php/php_mysql_insert.asp

Link to post
Share on other sites

As far as i know, i've done everything correctly. Nothing looks wrong. The code for connecting/creating the database. (connect.php)

<html><body><?php$conn=mysql_connect('localhost','','');if(!$conn){die('Could not connect: ' . mysql_error());}if(mysql_query("CREATE DATABASE tasks", $conn)){echo "Database Created";}else{echo "Error creating database: " . mysql_error();}mysql_select_db("tasks.mdb", $conn);$sql = "CREATE TABLE tasksTable(taskNumber int NOT NULL AOUT_INCREMENT,PRIMARY KEY(taskNumber),task Text,importance Text,dateAssigned Text,timeAssigned Text)";mysql_query($sql, $conn);mysql_close($conn);?></body></html>

Code for the insert (insert.php)

<html><body><?php$con = mysql_connect('localhost', '', '');if(!$con){die('Could not connect: ' . mysql_error());}mysql_select_db('tasks.mdb', $con);$sql = "INSERT INTO taskTable (Task, Importance, DateAssigned, TimeAssigned) VALUES ('$_POST[task]', '$_POST[importance]', '$_POST[date]', '$_POST[time]')";if(!mysql_query($sql, $con)){die('Error: ' . mysql_error());}echo "1 record added";mysql_close($con);?></body></html>

Code for viewing the database (viewTasks.php)

<html><body><?php$con = mysql_connect("localhost", "", "");if(!$con){die('Could not connectL ' . mysql_error());}mysql_select_db("tasks.mdb", $con);$result = mysql_query("SELECT * FROM tasksTable");echo "<table border='1' width='100%'><tr><th width='40%'>Task</th><th width='20%'>Importance</th><th width='20%'>Date Assigned</th><th width='20%'>Time Assigned</th></tr>";while($row = mysql_fetch_array($result));{echo "<tr>";echo "<td>" . $row['Task'] . "</td>";echo "<td>" . $row['Importance'] . "</td>";echo "<td>" . $row['DateAssigned'] . "</td>";echo "<td>" . $row['TimeAssigned'] . "</td>";echo "</tr>";}mysql_close($con);?></body></html>

If there is something wrong with the code, then i can not see where it is at. line 29 should work, but as I read from the topic you posted, the $result will return a boolean of false when it does not connect, which can cause the boolean error.

Link to post
Share on other sites

mysql_select_db('tasks.mdb', $con); You use the mysql extension to connect to a MySQL database, not an Access database. The mysql extension is only for MySQL, not for MS SQL Server or Oracle or Access or PostgreSQL or any other database. You can add error reporting to that statement to see the error: mysql_select_db('tasks.mdb', $con) or exit(mysql_error()); The error will be that a database called "tasks.mdb" doesn't exist. According to your other file the database is called "tasks", not "tasks.mdb".

Link to post
Share on other sites

ok, so what are you trying to connect to? An access DB or a MySQL DB? mysql_query is only for connecting to MySQL databases, I thought we were reviewing your MySQL code...?

Link to post
Share on other sites
You use the mysql extension to connect to a MySQL database, not an Access database.
Okay, what do i do? Just take the .mdb off? I think my error was in that my html was not opening/calling the connect.php, which would make it to where the database is not created. But now i am getting an error where i cannot create the database. Error creating database: Access denied for user ''@'localhost' to database 'tasks' Do i need to be the admin of the computer?
Link to post
Share on other sites

I never create databases through PHP, I always use another tool to do that. Regardless, you only create a database once. If you have that script to create it then you only need to create it once, but you also need to apply permissions to it. You're not using a username and password to connect to MySQL, so that might be one problem. Anyway, on most servers there is a control panel like cPanel to create a new database and give a database user permission to use it. I usually use that to create my databases, but mostly because my servers have cPanel installed. You can also use phpMyAdmin to create a database if the user you're logged in as has permission to create databases. You can also give a user permission to use a database through phpMyAdmin. If you haven't used that before, I would start by downloading and installing phpMyAdmin. Even outside of creating databases it's the most-used tool to administer a MySQL server. I always create my databases and tables through phpMyAdmin rather than through a PHP script or even the MySQL console.

Link to post
Share on other sites

from your previous post, the names of the tables were different

Code for the insert (insert.php)
$sql = "INSERT INTO taskTable (Task, Importance, DateAssigned, TimeAssigned) VALUES ('$_POST[task]', '$_POST[importance]', '$_POST[date]', '$_POST[time]')";

Code for viewing the database (viewTasks.php)

$result = mysql_query("SELECT * FROM tasksTable");

Edited by thescientist
Link to post
Share on other sites

Also, just so you know, MySQL does not use filenames for database, table, or field names inside queries. The names of those are stored internally. The period in a query is used to separate servers, databases, tables, and fields. You could specify to get a certain field in a certain table in a certain database like this: SELECT database_name.table_name.field_name FROM ... I use queries like that to find records in one table that aren't in another table in a different database, e.g.: SELECT id FROM db1.table WHERE id NOT IN (SELECT id FROM db2.table) So the periods are used to separate servers, databases, tables, and fields, you would never put a filename in there. Telling it you want to select the database "test.mdb" means that you want to select the database "mdb" on the server "test", not a database called "test.mdb".

Link to post
Share on other sites
Post the updated code for that.
$con = mysql_connect("localhost", "root", "");
from your previous post, the names of the tables were different
Yeah, i noticed that as well, so i fixed it. It is tasksTable.
You could specify to get a certain field in a certain table in a certain database like this: SELECT database_name.table_name.field_name FROM ...
will $result = mysql_query("SELECT * FROM tasks.db.tasksTable"); work?
Link to post
Share on other sites
will $result = mysql_query("SELECT * FROM tasks.db.tasksTable"); work?
maybe you should reread his post, or the references I linked for you.
Also, just so you know, MySQL does not use filenames for database, table, or field names inside queries. The names of those are stored internally. The period in a query is used to separate servers, databases, tables, and fields. You could specify to get a certain field in a certain table in a certain database like this: SELECT database_name.table_name.field_name FROM ... I use queries like that to find records in one table that aren't in another table in a different database, e.g.: SELECT id FROM db1.table WHERE id NOT IN (SELECT id FROM db2.table) So the periods are used to separate servers, databases, tables, and fields, you would never put a filename in there. Telling it you want to select the database "test.mdb" means that you want to select the database "mdb" on the server "test", not a database called "test.mdb".
Just follow their examples, especially the syntax of their queries.
Link to post
Share on other sites

I've followed the examples step by step. insert.php mysql_select_db('tasks', $con);$sql = "INSERT INTO tasks.taskstable (task, importance, dateAssigned, timeAssigned) VALUES ('$_POST[task]', '$_POST[importance]', '$_POST[date]', '$_POST[time]')"; viewTasks.php mysql_select_db("tasks", $con);$result = mysql_query("SELECT * FROM tasks.taskstable");while($row = mysql_fetch_array($result));{ echo "<tr>"; echo "<td>" . $row['task'] . "</td>"; echo "<td>" . $row['importance'] . "</td>"; echo "<td>" . $row['dateAssigned'] . "</td>"; echo "<td>" . $row['timeAssigned'] . "</td>"; echo "</tr>";} But the viewTasks does not work. it is empty still.

Link to post
Share on other sites
I've followed the examples step by step. insert.php mysql_select_db('tasks', $con);$sql = "INSERT INTO tasks.taskstable (task, importance, dateAssigned, timeAssigned) VALUES ('$_POST[task]', '$_POST[importance]', '$_POST[date]', '$_POST[time]')"; viewTasks.php mysql_select_db("tasks", $con);$result = mysql_query("SELECT * FROM tasks.taskstable"); while($row = mysql_fetch_array($result));{echo "<tr>";echo "<td>" . $row['task'] . "</td>";echo "<td>" . $row['importance'] . "</td>";echo "<td>" . $row['dateAssigned'] . "</td>";echo "<td>" . $row['timeAssigned'] . "</td>";echo "</tr>";} But the viewTasks does not work. it is empty still.
do you still have all your error handling. you should make sure that it's in there. like you had before, i.e. the connection
$conn=mysql_connect('localhost','','');if(!$conn){die('Could not connect: ' . mysql_error());}

and on the query

$sql = "INSERT INTO taskTable (Task, Importance, DateAssigned, TimeAssigned) VALUES ('$_POST[task]', '$_POST[importance]', '$_POST[date]', '$_POST[time]')";if(!mysql_query($sql, $con)){die('Error: ' . mysql_error());}echo "1 record added";mysql_close($con);

Edited by thescientist
Link to post
Share on other sites

Yeah, the error handeling is still there, but i get no errors. I added a return button to return back to index.html which is where an iframe pulls the viewTasks.php via src. <iframe width="100%" height="100%" src="viewTasks.php"> </iframe> Could it be that?

Link to post
Share on other sites

im not sure. can you just post all your most recent code for all this including the HTML of your form)? If nothing sticks out, I'll try running it locally myself. Also, if you add any echo statements to your PHP scripts, do you at least see that show? Do you have phpMyAdmin? Is the data showing up at all from the INSERT? You should try echoing out the SQL query and try running that through phpMyAdmin as well.

Edited by thescientist
Link to post
Share on other sites

index.html

<html><head><title>Tasks to Complete</title></head><body><table border="1" width="100%" height="100%"><tr width="100%" height="10%">  <td colspan="2" height="10%">   <h2 align="center">Welcome to the Task List</h2>  </td></tr><tr width="100%" height="90%">  <td width="50%" height="100%">   <form action="insert.php" method="post">    <fieldset>	 <legend>Insert Tasks Here:</legend>	 <textarea rows="15" cols="50" name="task"></textarea>	 </br>	 </br>	 <input type="radio" name="importance" id="highly important" value="Highly Important">Highly Important</input>	 </br>	 <input type="radio" name="importance" id="important" value="Important">Important</input>	 </br>	 <input type="radio" name="importance" id="can wait" value="Can Wait">Can Wait</input>	 </br>	 <p>Date Assigned    <input type="text" name="date" id="date" /></p>	 <p>Time Assigned    <input type="text" name="time" id="time" /></p>	 <input type="submit" id="submit task" value="Submit Task" />    </fieldset>   </form>  </td>  <td width="50%" height="100%">     <iframe width="100%" height="100%" src="viewTasks.php">   </iframe>  </td></tr></table></body></html>

insert.php

<html><body><?php$con = mysql_connect('localhost', 'root', '');if(!$con){die('Could not connect: ' . mysql_error());}mysql_select_db('tasks', $con);$sql = "INSERT INTO tasks.taskstable (task, importance, dateAssigned, timeAssigned) VALUES ('$_POST[task]', '$_POST[importance]', '$_POST[date]', '$_POST[time]')";if(!mysql_query($sql, $con)){die('Error: ' . mysql_error());}echo "1 record added";mysql_close($con);?><form method="link" action="index.html"><input type="Submit" value="Return" /></form></body></html>

viewTasks.php

<html><body><?php$con = mysql_connect("localhost", "root", "");if(!$con){die('Could not connectL ' . mysql_error());}mysql_select_db("tasks", $con);$result = mysql_query("SELECT * FROM tasks.taskstable");if(!$result){die('Invalid query: ' . mysql_error());}echo "<table border='1' width='100%'><tr><th width='40%'>Task</th><th width='20%'>Importance</th><th width='20%'>Date Assigned</th><th width='20%'>Time Assigned</th></tr>";while($row = mysql_fetch_array($result));{echo "<tr>";echo "<td>" . $row['task'] . "</td>";echo "<td>" . $row['importance'] . "</td>";echo "<td>" . $row['dateAssigned'] . "</td>";echo "<td>" . $row['timeAssigned'] . "</td>";echo "</tr>";}mysql_close($con);?></body></html>

connect.php (first time use)

<html><body><?php$conn=mysql_connect('localhost','root','');if(!$conn){die('Could not connect: ' . mysql_error());}if(mysql_query("CREATE DATABASE tasks", $conn)){echo "Database Created";}else{echo "Error creating database: " . mysql_error();}mysql_select_db("tasks", $conn);$sql = "CREATE TABLE tasksTable(taskNumber int NOT NULL AUTO_INCREMENT,PRIMARY KEY(taskNumber),task Text,importance Text,dateAssigned Text,timeAssigned Text)";mysql_query($sql, $conn);mysql_close($conn);?><script language="javascript">function redirect () {  setTimeout("go_now()",10000);}function go_no() {  window.location.href = "index.html"}</script></body></html>

Link to post
Share on other sites

what about some of my questions?

Also, if you add any echo statements to your PHP scripts, do you at least see that show? Do you have phpMyAdmin? Is the data showing up at all from the INSERT? You should try echoing out the SQL query and try running that through phpMyAdmin as well.
Link to post
Share on other sites

this is your problem

while($row = mysql_fetch_array($result));

remove the semicolon at the end. you are prematurely ending the while loop.

Link to post
Share on other sites

Okay... After much work on this and help from everyone, i've gotten the insert and viewTasks completed. Now, i need help with another problem that has risen. I created a delete.php for deleting tasks in the list. i've gotten to where it will delete the task from the list via and AUTO_INCREMENT number. 1. How do I check to see if the table has nothing in it (empty)?2. How do I reset the AUTO_INCREMENT?3. In thinking of future, how do I delete a task from w number, and decrease the AUTO_INCREMENT number from there Number 3 example: I have 10 tasks. I delete number 4 from the table. How do i decrement the numbers from 5 to 10? delete.php so far

<html><body> <?php$con = mysql_connect("localhost", "root", "");if(!$con){die('Could not connect: ' . mysql_error());}$tasknumber=$_POST['number'];mysql_select_db("tasks", $con);$delete = "DELETE FROM tasks.taskstable WHERE taskNumber=$tasknumber";if(!mysql_query($delete, $con)){die('Error: ' . mysql_error());}mysql_query("UPDATE tasks.taskstable SET taskNumber=taskNumber - 1"); //Reset AUTO_INCREMENT$result = mysql_query("SELECT * FROM tasks.taskstable"); $row = mysql_fetch_array($result);if(isset($row['tasknumber'])){mysql_query("ALTER TABLE tasks.taskstable AUTO_INCREMENT = 1");echo "No tasks left";}mysql_close($con);?> <form method="link" action="index.html"><input type="Submit" value="Return" /></form> </body></html>

Edited by ProSpartan
Link to post
Share on other sites

You can use COUNT to get the number of records in a table: SELECT COUNT(*) AS num FROM table The field will be called "num", or whatever you name it. As for the auto-increment, there's no reason to reset it. Just let it keep increasing, there's no problem with that.

Link to post
Share on other sites

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.   Paste as plain text instead

  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.

Loading...
×
×
  • Create New...