Trouble with a mySQL query()

[reub77]

I'm having a hard time figuring out how to solve a problem I have with some code. I'm using an image button to submit an update to a database. A similar chunk of code successfully works to delete field elements. All I'm trying to do is update field elements the same way, but a little more complex. The problem has to be with how I've designed my mysql query. Either that or I've biffed it somewhere on syntax.

The errors reads like this:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/shabangw/public_html/editProjLeads.php on line 8

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/shabangw/public_html/editProjLeads.php on line 9

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/shabangw/public_html/editProjLeads.php on line 13

The chunk of code below is called editProjLeads.php:

    <?PHP
	if($Edit_x){
          $conn = db_connect(); 
	      //declare query
          $query = 'SELECT id, companyName, contact, phoneNum, email, street, city, provState, country, postalCode, url, contractStatus FROM currProjects WHERE id="'.$ID.'"';
          //execute Query
          $result = mysql_query($query);
		  $jobid = mysql_query($result, 'id');
		  $Company = mysql_query($result, 'companyName');
		  $cont = mysql_result($result, 'contact');
          $phone = mysql_result($result, 'phoneNum');
		  $Email = mysql_result($result, 'email');
		  $Street = mysql_query($result, 'street');
          $City = mysql_result($result, 'city');              
          $Prov = mysql_result($result, 'provState');              
          $Country = mysql_result($result, 'country'); 
		  $postal = mysql_result($result, 'postalCode');
		  $URL = mysql_result($result, 'url');
          $contStat = mysql_result($result, 'contractStatus');
          mysql_close();
		  if($UpdateProjLeads) {
		       $conn = db_connect(); 
               //declare query
               $query = 'UPDATE currProjects SET companyName="'.$comp.'", contact="'.$Contact.'", phoneNum="'.$Phone.'", email="'.$E_mail.'", provState="'.$Prov.'", country="'.$Cntry.'", postalCode="'.$Postal.'", contractStatus="'.$contStat.'", street="'.$St.'", city="'.$CIty.'", url="'.$Url.'" WHERE id="'.$ID.'"';
               //execute query
               $result = mysql_query($query);
         }
	?>
	<div id="editWin" class="drag">
	<form action="<?php echo $PHP_SELF ?>" method="post" name="editProjLeads">
	  <input name="ID" type="hidden" value="<?PHP echo $jobid ?>">
      <div align="center">Company Name: 
        <select name="comp">
        <?PHP
		  $conn = db_connect(); 
          //declare query
          $query = 'SELECT companyName FROM currProjects';
          //Execute Query
          $result = mysql_query($query);
          $x = 0;
          while ($x < mysql_num_rows($result)) :
              $company = mysql_result($result, $x, 'companyName');
              print "<option value='$company' ";
			  if ($company==$Company) print "selected"; 
			  print">$company</option>";   
              $x++;
          endwhile;
          print "</select>";
          mysql_close();			  
		?>
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Contact Name: &nbsp; 
        <input name="Contact" type="text" value="<?PHP echo $cont ?>">
        <br>
        <br>
        Phone: 
        <input name="Phone" type="text" value="<?PHP echo $phone ?>">
        &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;E-mail:&nbsp; 
        <input name="E_mail" type="text" value="<?PHP echo $Email ?>">
        <br>
        <br>
        Street: 
        <input name="St" type="text"  value="<?PHP echo $Street ?>">
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; City:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
        <input name="CIty" type="text" value="<?PHP echo $City ?>">
        <br>
        <br>
        State/Province:
        <select name="Prov">
          <option value="Alberta" <?PHP if($prov=="Alberta"){ echo("selected");}?> >Alberta</option>
          <option value="British Columbia" <?PHP if($prov=="British Columbia"){ echo("selected");}?> >British Columbia</option>
          <option value="Saskatchewan" <?PHP if($prov=="Saskatchewan"){ echo("selected");}?> >Saskatchewan</option>
          <option value="Manitoba" <?PHP if($prov=="Manitoba"){ echo("selected");}?> >Manitoba</option>
          <option value="Yukon" <?PHP if($prov=="Yukon"){ echo("selected");}?> >Yukon</option>
          <option value="North West Territories" <?PHP if($prov=="North West Territories"){ echo("selected");}?> >North West Territories</option>
          <option value="Nova Scotia" <?PHP if($prov=="Nova Scotia"){ echo("selected");}?> >Nova Scotia</option>
          <option value="Ontario" <?PHP if($prov=="Ontario"){ echo("selected");}?> >Ontario</option>
          <option value="Quebec" <?PHP if($prov=="Quebec"){ echo("selected");}?> >Quebec</option>
          <option value="Newfoundland" <?PHP if($prov=="Newfoundland"){ echo("selected");}?> >Newfoundland</option>
          <option value="Nunavut" <?PHP if($prov=="Nunavut"){ echo("selected");}?> >Nunavut</option>
          <option value="Prince Edward Island" <?PHP if($prov=="Prince Edward Island"){ echo("selected");}?> >Prince Edward Island</option>
          <option value="New Brunswick" <?PHP if($prov=="New Brunswick"){ echo("selected");}?> >New Brunswick</option>
        </select>
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Country: 
        <select name="Cntry">
          <option value="Canada" <?PHP if($Country=="Canada"){ echo("selected");}?> >Canada</option>
          <option value="United States" <?PHP if($Country=="United States"){ echo("selected");}?> >United States</option>
          <option value="Ireland" <?PHP if($Country=="Ireland"){ echo("selected");}?> >Ireland</option>
        </select>
        <br>
        <br>
        Postal Code: 
        <input name="Postal" type="text" value="<?PHP echo $postal ?>">
        &nbsp;&nbsp;&nbsp;URL: 
        <input type="text" name="Url" value="<?PHP echo $URL ?>">
        &nbsp;<br>
        &nbsp;<br>
        Contract Status: 
        <select name="contStat">
          <option value="active" <?PHP if($contStatus=="active"){ echo("selected");}?> >active</option>
          <option value="none" <?PHP if($contStatus=="none"){ echo("selected");}?> >none</option>
		  <option value="expired" <?PHP if($contStatus=="expired"){ echo("selected");}?> >expired</option>
        </select>
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
        <br>
        <br>
        <input type="submit" name="UpdateProjLeads" value="Update">
        <input type="reset" name="Submit2" value="Reset">
      </div>
    </form>
  </div>	
  <?PHP } ?>

The main code which is called projLeads.php is attached as a text file. Convert the .txt to .php. If you can help me with the problem then hats off to you. My brain is going numb.

[jburfield]

You are not supplying your db link along with your query.

You have:

 $conn = db_connect();

$result = mysql_query($query); 

So, if I assume that db_connect returns a connection called $db_connect, and it has been made global in the db_connect() function, you need to add this to your $result line:

$result = mysql_query($query, $db_connect);

Try that.

-- Jason

[gid]

$jobid = mysql_query($result, 'id');

shouldn't this be:
$jobid = mysql_result($result, 'id');

you should really look at mysql_fetch_object(), this way your global name space doesn't get all junked up with lots of variable names. Besides, numerous calls to mysql_result() is rather inefficient.

[reub77]

The way I have it works for other queries. It may not look the best but it works. The db_connect(); is calling a function that is in an include file. I don't think the way you put it along with the query would work anyways because you left out the "()".

So I could change it, but I'm thinking 98% that it's something else.

[reub77]

Why would this be any better and would I use it the same way? Any ways I don't think it will solve my problem or will it?

[jburfield]

Originally posted by reub77
The way I have it works for other queries. It may not look the best but it works. The db_connect(); is calling a function that is in an include file. I don't think the way you put it along with the query would work anyways because you left out the "()".

So I could change it, but I'm thinking 98% that it's something else.

Exactly....that function creates a connection to your database, but you need to include that connection with your mysql_query() or you are trying to query with no database connection.

Re-read the post. It says that if you function is RETURNING a value of $db_connect, meaning that you connection inside that include file, inside the function is something like this:
$db_connect = mysql_connect("server", "name", password");

Then you need to supply that with the query argument:

mysql_query($query, $db_connect);

mysql_query($query); will NOT work by itself.

-- Jason

[gid]

$conn = db_connect();
//declare query
$query = 'SELECT id, companyName, contact, phoneNum, email, street, city, provState, country, postalCode, url, contractStatus FROM currProjects WHERE id="'.$ID.'"';
//execute Query
$result = mysql_query($query);
$jobid = mysql_query($result, 'id');

Look at the last two lines, they're both executing a query. The last line is definite wrong, you want to call mysql_result() not mysql_query(), mysql_query always executes a query, mysql_result fetches a single variable, mysql_fetch_object fetches all the fields in a row as an object. So this code:

$result = mysql_query($query);
$jobid = mysql_query($result, 'id');
$Company = mysql_query($result, 'companyName');
$cont = mysql_result($result, 'contact');
$phone = mysql_result($result, 'phoneNum');
$Email = mysql_result($result, 'email');
$Street = mysql_query($result, 'street');
$City = mysql_result($result, 'city');
$Prov = mysql_result($result, 'provState');
$Country = mysql_result($result, 'country');
$postal = mysql_result($result, 'postalCode');
$URL = mysql_result($result, 'url'); 

could change into this code: (also note the above code how you have mysql_query and mysql_result interchanged. These functions are not interchangeable, they both do entirely different things. If you don't understand this, then read the manual over again.)

$result = mysql_query($query);
$row = mysql_fetch_object($result);
//access the object like this:
echo $row->id;
echo $row->phone;
echo $row->email;

[gid]

Originally posted by jburfield


Exactly....that function creates a connection to your database, but you need to include that connection with your mysql_query() or you are trying to query with no database connection.

Re-read the post. It says that if you function is RETURNING a value of $db_connect, meaning that you connection inside that include file, inside the function is something like this:
$db_connect = mysql_connect("server", "name", password");

Then you need to supply that with the query argument:

mysql_query($query, $db_connect);

mysql_query($query); will NOT work by itself.

-- Jason

from the php manual:
resource mysql_query ( string query [, resource link_identifier [, int result_mode]])

with mysql, the link_identifier is optional, if none is specified, the current mysql connection is used, yes, even if it's called from inside a function. his problem is as I noted above, twice.

[jburfield]

You are correct, that is his problem. But if you take the time to read what I was writing you would see that I was trying to explain something that had been written before.

And on a side note:
Do you know if he is only using one db connection in his code? I don't. Hence, adding the mysql_query($query, $connection); note.

It's ALWAYS safer to do that when you do not know if there are more than one connection. Period. That way you know you are using the correct one.

-- Jason

[gid]

Well I guess I wrongly assumed that he was using only one database connection. Plus I figured if he's using two mysql database connections, then he'd be smart enough to specify the link identifier.

mysql_query($query); will NOT work by itself.

Mainly I was trying to correct that incorrect blanket statement. If I rubbed you the wrong way while doing it, then I appologize. I've never been known for my people skills, that's why I'm a programmer. 🙂 Peace.

[reub77]

This is my db_connect(); function file. I call this whenever I query the database. It opens the connection to the database. I have taken out some of the passwords for obvious reasons.

<?PHP
function db_connect(){
$result = @mysql_connect('localhost', '#####', '#####');
if(!$result)
return(false);
if(!@mysql_select_db('#####')){
return(false);
return($result);
}
}
?>

Am I still a big screw up?

[gid]

Nope, looks fine to me. Did you get your code to work?

[reub77]

I was using that code for the connections. You harrassed me for having that code. Of course you didn't see it though because it was in an include file. It's handy for when you need to recycle the connections. So then I was right all along.

My only errors were using too many queries of the database. Makes sense seeing I accidently put mysql_query instead of mysql_result on three of the lines. I was happy to find out that the fetch_row method would be better. No, I haven't implemented it yet mostly because I know that was the problem all along.

I'm gonna do it now though.

[gid]

I was using that code for the connections. You harrassed me for having that code.

I meant nothing of the sort. Having db_connect() as a function is perfectly fine, at no point did I ever make and judgements about that piece of code or how you implemented it. I use something very similar oh all my sites that I build.

When I said "Nope, looks fine to me." that was direct response to your only question that you posed in your previous post. That question being: "Am I still a big screw up? I worded my sentence a little ackward, I'll definitely give you that. Maybe I should quote more, but dang this text area box is small and quoting is a pain.

If this doesn't clear things up, then I think I'm missing something about this conversation. At no point in time whatsoever did I attempt to be hostile. I appologize, if I appeared that I did. And I did, could you please point out what I said that you took be hostile, so I can not say that in the future. Thanks.

[reub77]

Sorry, I read through the last messages and realized you were the one that helped the most. However the other guy said I didn't listen to what he was saying. I just don't think he saw the complete picture. Sorry, so many messages, so many people.