Jump to content

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given i

pablo1988

By pablo1988
in PHP Coding Help

Recommended Posts

pablo1988 Newbie

pablo1988

Posted
Posted

I am getting the below error message:

 

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\Program Files (x86)\EasyPHP-5.3.9\www\a.php on line 78

 

The two issues are:

 

1. The red text I need to somehow use the DISTINCT function as it is duplicating a person for every skill they have.

 

2. The blue text is causing the error above, if I remove the join it works but assigns every possible skill to the person (because skill table and resource table are not joined). I therefore need the join there but without the error.

 

My code is below:

 

<!DOCTYPE  HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"  "http://www.w3.org/TR/html4/loose.dtd">

<html>

  <head>

    <meta  http-equiv="Content-Type" content="text/html;  charset=iso-8859-1">

    <title>Search  Contacts</title>

 

<style  type="text/css" media="screen">

ul  li{

  list-style-type:none; }

</style> 

  </head>

 

  <p><body>

    <h3>Search  Contacts Details</h3>

    <p>You  may search either by first or last name</p>

    <form  method="post" action="a.php?go"  id="searchform">

      <input  type="text" name="name">

      <input  type="submit" name="submit" value="Search">

    </form>

 

<?php

  if(isset($_POST['submit'])){

  if(isset($_GET['go'])){

  if(preg_match("/^[  a-zA-Z]+/", $_POST['name'])){

  $name=$_POST['name'];

 

//connect  to the database

  $db=mysql_connect  ("127.0.0.1", "root",  "") or die ('I cannot connect to the database  because: ' . mysql_error());

 

//-select  the database to use

  $mydb=mysql_select_db("resource matrix");

 

 

//-query  the database table

 

 

 

  $sql="SELECT * FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '%" . $name .  "%' OR Last_Name LIKE '%" . $name ."%' OR Skill_Name LIKE '%" . $name ."%'";

 

//-run  the query against the mysql query function

  $result=mysql_query($sql);

 

//-create  while loop and loop through result set

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

          $First_Name  =$row['First_Name'];

          $Last_Name=$row['Last_Name'];

          $Resource_ID=$row['Resource_ID'];

 

//-display the result of the array

  echo "<ul>\n";

  echo "<li>" . "<a  href=\"a.php?id=$Resource_ID\">"  .$First_Name . " " . $Last_Name .  "</a></li>\n";

  echo "</ul>";

  }

  }

  else{

  echo  "<p>Please enter a search query</p>";

  }

  }

  }

 

//end of our letter search script

if(isset($_GET['id'])){

$contactid=$_GET['id'];

 

//connect  to the database

$db=mysql_connect  ("127.0.0.1", "root",  "") or die ('I cannot connect to the database  because: ' . mysql_error());

 

//-select  the database to use

$mydb=mysql_select_db("resource matrix");

 

//-query  the database table

$sql="SELECT * FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE Resource_ID=" . $contactid;

 

//-run  the query against the mysql query function

$result=mysql_query($sql);

 

//-create  while loop and loop through result set

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

      $First_Name =$row['First_Name'];

            $Last_Name=$row['Last_Name'];

            $Mobile_Number=$row['Mobile_Number'];

            $Email_Address=$row['Email_Address'];

    $Level=$row['Level'];

    $Security_Cleared=$row['Security_Cleared'];

            $Contract_Type=$row['Contract_Type'];

            $Contract_Expiry=$row['Contract_Expiry'];

            $Day_Rate=$row['Day_Rate'];

            $Post_Code=$row['Post_Code'];

    $Skill_Name=$row['Skill_Name'];

 

//-display  the result of the array

echo  "<ul>\n";

echo  "<li>" . $First_Name . " " . $Last_Name .  "</li>\n";

echo  "<li>" . $Mobile_Number . "</li>\n";

echo  "<li>" . "<a href=mailto:" . $Email_Address .  ">" . $Email_Address . "</a></li>\n";

echo  "<li>" . $Level . "</li>\n";

echo  "<li>" . $Security_Cleared . "</li>\n";

echo  "<li>" . $Contract_Type . "</li>\n";

echo  "<li>" . $Contract_Expiry . "</li>\n";

echo  "<li>" . $Day_Rate . "</li>\n";

echo  "<li>" . $Post_Code . "</li>\n";

echo  "<li>" . $Skill_Name . "</li>\n";

echo  "</ul>";

}

}

 

 

 

?>

 

 

 

 

</body>

</html> 

 

 

 

 

AyKay47 Member

AyKay47

Posted
Posted

my money is on an ambiguity issue with Resource_ID, but add this to the query:

 

$sql="SELECT * FROM resource l
inner join resource_skill ln on l.Resource_ID = ln.Resource_ID
inner join skill n on ln.Skill_ID = n.Skill_ID
WHERE Resource_ID=" . $contactid;

//-run  the query against the mysql query function
$result=mysql_query($sql) or die(mysql_error() . '<br />' . $sql);

 

post the results.

pablo1988 Newbie

pablo1988

Posted
  • Author
Posted

Thanks AyKay47, see results below:

 

Column 'Resource_ID' in where clause is ambiguous

SELECT * FROM resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID inner join skill n on ln.Skill_ID = n.Skill_ID WHERE Resource_ID=4

 

 

pablo1988 Newbie

pablo1988

Posted
  • Author
Posted

Do you mean just change Resource_ID to resource.Resource_ID (resource being one of the tables Resource_ID is located)?

 

I have added that but get the below error:

 

Unknown column 'resource.Resource_ID' in 'where clause'

SELECT * FROM resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID inner join skill n on ln.Skill_ID = n.Skill_ID WHERE resource.Resource_ID=4

 

Is that what you mean by table identifier?

 

Thanks for your help

 

 

pablo1988 Newbie

pablo1988

Posted
  • Author
Posted

I see, that has done it! Thanks a lot!

 

Now for the next challenge...getting rid of the duplicates.

 

The highlighted RED text below duplicates names dependant on the number of skills they have assigned to them.

 

-> I need to just show the First and Last Name once.

 

The highlighted BLUE text below duplicates all of the persons details for each skill.

 

-> I need to show the person details once other than the skill which I need to list all assigned to the person.

 

I'm sure it's some use of DISTINCT but not sure where and how.

 

Thanks again AyKay47

 

 

<!DOCTYPE  HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"  "http://www.w3.org/TR/html4/loose.dtd">

  <html>

    <head>

      <meta  http-equiv="Content-Type" content="text/html;  charset=iso-8859-1">

      <title>Search  Contacts</title>

 

<style  type="text/css" media="screen">

  ul  li{

    list-style-type:none;    }

  </style>

    </head>

 

    <p><body>

      <h3>Search  Contacts Details</h3>

      <p>You  may search either by first or last name</p>

      <form  method="post" action="a.php?go"  id="searchform">

        <input  type="text" name="name">

        <input  type="submit" name="submit" value="Search">

          </form>

 

<?php

    if(isset($_POST['submit'])){

    if(isset($_GET['go'])){

    if(preg_match("/^[  a-zA-Z]+/", $_POST['name'])){

    $name=$_POST['name'];

   

//connect  to the database

    $db=mysql_connect  ("127.0.0.1", "root",  "") or die ('I cannot connect to the database  because: ' . mysql_error());

   

//-select  the database to use

    $mydb=mysql_select_db("resource matrix");

   

 

//-query  the database table

 

 

 

    $sql="SELECT * FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '%" . $name .  "%' OR Last_Name LIKE '%" . $name ."%' OR Skill_Name LIKE '%" . $name ."%'";

   

//-run  the query against the mysql query function

    $result=mysql_query($sql);

   

//-create  while loop and loop through result set

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

            $First_Name  =$row['First_Name'];

            $Last_Name=$row['Last_Name'];

            $Resource_ID=$row['Resource_ID'];

   

//-display the result of the array

    echo "<ul>\n";

    echo "<li>" . "<a  href=\"a.php?id=$Resource_ID\">"  .$First_Name . " " . $Last_Name .  "</a></li>\n";

    echo "</ul>";

    }

    }

    else{

    echo  "<p>Please enter a search query</p>";

    }

    }

    }

 

//end of our letter search script

if(isset($_GET['id'])){

$contactid=$_GET['id'];

 

//connect  to the database

$db=mysql_connect  ("127.0.0.1", "root",  "") or die ('I cannot connect to the database  because: ' . mysql_error());

 

//-select  the database to use

$mydb=mysql_select_db("resource matrix");

 

$sql="SELECT * FROM resource l

inner join resource_skill ln on l.Resource_ID = ln.Resource_ID

inner join skill n on ln.Skill_ID = n.Skill_ID

WHERE l.Resource_ID=" . $contactid;

 

//-run  the query against the mysql query function

$result=mysql_query($sql) or die(mysql_error() . '<br />' . $sql);

 

//-create  while loop and loop through result set

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

        $First_Name =$row['First_Name'];

            $Last_Name=$row['Last_Name'];

            $Mobile_Number=$row['Mobile_Number'];

            $Email_Address=$row['Email_Address'];

      $Level=$row['Level'];

      $Security_Cleared=$row['Security_Cleared'];

            $Contract_Type=$row['Contract_Type'];

            $Contract_Expiry=$row['Contract_Expiry'];

            $Day_Rate=$row['Day_Rate'];

            $Post_Code=$row['Post_Code'];

      $Skill_Name=$row['Skill_Name'];

 

//-display  the result of the array

echo  "<ul>\n";

echo  "<li>" . $First_Name . " " . $Last_Name .  "</li>\n";

echo  "<li>" . $Mobile_Number . "</li>\n";

echo  "<li>" . "<a href=mailto:" . $Email_Address .  ">" . $Email_Address . "</a></li>\n";

echo  "<li>" . $Level . "</li>\n";

echo  "<li>" . $Security_Cleared . "</li>\n";

echo  "<li>" . $Contract_Type . "</li>\n";

echo  "<li>" . $Contract_Expiry . "</li>\n";

echo  "<li>" . $Day_Rate . "</li>\n";

echo  "<li>" . $Post_Code . "</li>\n";

echo  "<li>" . $Skill_Name . "</li>\n";

echo  "</ul>";

}

}

 

 

 

  ?>

 

 

 

 

  </body>

  </html> 

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.