Mysql Problem

Question

BenzZz 0 Junior Poster

13 Years Ago

Hi,

I am receiving the following error when i run my code on localhost:

Error creating database: Can't create database 'library_db'; database exists
Warning: mysql_query() [function.mysql-query]: Access denied for user ''@'localhost' (using password: NO) in C:\Program Files\EasyPHP-5.3.6.0\www\databaseEx.php on line 45

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in C:\Program Files\EasyPHP-5.3.6.0\www\databaseEx.php on line 45

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\Program Files\EasyPHP-5.3.6.0\www\databaseEx.php on line 47
Fail

This is my actual PHP code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Log In Example</title>
</head>

<body>
<?php
$con = mysql_connect("127.0.0.1", "root");
if (!$con) {
  die('Could not connect: ' . mysql_error());
  }

// Create database
if (mysql_query("CREATE DATABASE library_db",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

// Create table
mysql_select_db("library_db", $con);
$sql = "CREATE TABLE Staff
(
UserId int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(UserId),
Username varchar(65) NOT NULL,
Password varchar(65) NOT NULL
)";

// Execute query
mysql_query($sql,$con);


mysql_query("INSERT INTO Staff (UserId, Username, Password)
VALUES (0000, 'Ben', 'Password')");

mysql_close($con);

$result = mysql_query("SELECT Username FROM Staff
WHERE Username = 'Ben'");

$count = mysql_num_rows($result);

if($count==1){
echo "$result has logged in";
}
else {
echo "Fail";
}
?>
</body>
</html>

From what i can see, it looks as though i can connect to sql as the database is created but then it does not allow me to query the database?

I'd really appreciate any help as i'm trying to press on with a project and without the database i'm stuck at the moment.

Thanks.

mysql php

3 Contributors
7 Replies
110 Views
1 Day Discussion Span
Latest Post 13 Years Ago Latest Post by BenzZz

rpv_sen 59 Junior Poster

13 Years Ago

Hi

in connection replace the below thing

$con = mysql_connect("localhost","root","");

ddymacek 15 Posting Whiz

13 Years Ago

I suggest you read up on mysql a little start here:
http://www.php.net/manual/en/function.mysql-result.php
the return item from a mysql_query() call is a resource that yet needs further evaluation.
To get data from the db you have to process the resource
There is more than one way to do it but here is simple and easy.

mysql_query("INSERT INTO Staff (UserId, Username, Password)
    VALUES (0000, 'Ben', 'Password')");
     
    $result = mysql_query("SELECT Username FROM Staff
    WHERE Username = 'Ben'");
     
    $count = mysql_num_rows($result);
    // new code lets get ben from the $result
   $name = '';
   if ($row = mysql_fetch_array($result)) {	
	$name = $row['Username'];
   }
   /* only an example, say your query was select * from Staff where username = 'Ben'
   if ($row = mysql_fetch_array($result)) {	
     $id = $row['UserId'];
     $name = $row['Username'];
     $pass = $row['Password'];
   }
   you could then pass $id,$name,$pass to whatever code you need.
   can also be used as a while loop but that is more advanced than what you are doing.  
   */
     if($count==1){
      // changed $result to $name
    echo "$name has logged in";
    }
    else {
    echo "Fail";
    }

hope that helps

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

BenzZz 0 Junior Poster · Answer 1 · 2011-08-16T14:44:48+00:00

After making that modification i get this instead:

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'root'@'localhost' (using password: NO) in C:\Program Files\EasyPHP-5.3.6.0\www\databaseEx.php on line 10
Could not connect: Access denied for user 'root'@'localhost' (using password: NO)

I checked last night on phpmyadmin on priviliges and root was granted all privileges so i can't see why access is denied?

rpv_sen 59 Junior Poster · Answer 2 · 2011-08-16T15:07:48+00:00

Hi

Try to close the Db connection at the end of the loop

BenzZz 0 Junior Poster · Answer 3 · 2011-08-16T15:31:54+00:00

Ahh i see i was closing the connection to early now from what you said. It looks like that has fixed the main problem with not being able to connect, Thanks :)

Now the result is:

Error creating database: Can't create database 'library_db'; database exists
Resource id #4 has logged in

I'm assuming i can get rid of the creating database error by just dropping it before it is created?

However, i'm confused as to why the result says:

Resource id #4 has logged in

When it should say:

Ben has logged in

as that part of my code is:

mysql_query("INSERT INTO Staff (UserId, Username, Password)
VALUES (0000, 'Ben', 'Password')");

$result = mysql_query("SELECT Username FROM Staff
WHERE Username = 'Ben'");

$count = mysql_num_rows($result);

if($count==1){
echo "$result has logged in";
}
else {
echo "Fail";
}

Thanks.

rpv_sen 59 Junior Poster · Answer 4 · 2011-08-17T10:01:48+00:00

Hi

Let we check the below thing

$result = "SELECT Username FROM Staff
    WHERE Username = 'Ben'";
	
	$res = mysql_query($result,$con);

if ($row = mysql_fetch_array($res))

BenzZz 0 Junior Poster · Answer 5 · 2011-08-17T15:02:41+00:00

Ah thanks, i've seen it wrote like that in other snippets of code but never really read into it.

I know standard SQL quite well but parts seem a bit different within this language for instance, having to process the result gathered from a query.

Thanks for the help :)