14 Replies - 876 Views - Last Post: 12 October 2011 - 09:49 PM Rate Topic: -----

#1 AlwaysLearningABT   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 10-October 11

PHP sql query. What's wrong?

Posted 10 October 2011 - 10:47 AM

I am developing a application to build something. There are multiple tables which hold all of the information needed for the front end to work obviously. The database part is all figured out. I am just having some trouble getting the Display field to automatically get the value from a different table. What am I doing wrong here?

	function updateEntranceDisplay($entranceID){
		// update entrance display picture
		$entranceID=makeSafe($entranceID);
		$style=sqlValue("select Ent_Style from Build_Entrance where Ent_Id='$entranceID'");
		$display=sqlValue("select ES_Graphic from Entrance_Styles where ES_Name='$style'");		
		sql("update Build_Entrance set Ent_Display='$display' where Ent_Id='$entranceID'");
	}


If I manually pull the value of the ES_Graphic field and paste it into the value (backend) of the Display field it works fine, as in it displays the way I want it too. The value, which would be a picture name 24783924_347249.jpg or something of the likes is simply not transferring to the new field I want it to. Any help is GREATLY appreciated.

Is This A Good Question/Topic? 0
  • +

Replies To: PHP sql query. What's wrong?

#2 CTphpnwb   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3795
  • View blog
  • Posts: 13,738
  • Joined: 08-August 08

Re: PHP sql query. What's wrong?

Posted 10 October 2011 - 10:53 AM

Where are your definitions of sqlValue() and sql(), and where do you call updateEntranceDisplay()?
Was This Post Helpful? 0
  • +
  • -

#3 AlwaysLearningABT   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 10-October 11

Re: PHP sql query. What's wrong?

Posted 10 October 2011 - 11:03 AM

I have a hooks file that calls it.

function Build_Entrance_after_update($data, $memberInfo, &$args){
		updateEntranceDisplay($data['selectedID']);
		return TRUE;
	}


Maybe I did not define the sqlValue and sql() the way I thought I did. I can't seem to find it now. Lol. How
Was This Post Helpful? 0
  • +
  • -

#4 CTphpnwb   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3795
  • View blog
  • Posts: 13,738
  • Joined: 08-August 08

Re: PHP sql query. What's wrong?

Posted 10 October 2011 - 11:08 AM

How do you expect us to help you?
Was This Post Helpful? 0
  • +
  • -

#5 AlwaysLearningABT   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 10-October 11

Re: PHP sql query. What's wrong?

Posted 10 October 2011 - 11:13 AM

Sorry I found it.
I have a file called incFunctions in the admin folder which is where sql and sqlValue are defined.

########################################################################
	function sqlValue($statment){
		// executes a statment that retreives a single data value and returns the value retrieved
		if(!$res=sql($statment, $eo)){
			return FALSE;
		}
		if(!$row=mysql_fetch_row($res)){
			return FALSE;
		}
		return $row[0];
	}


and here
if(!function_exists('sql')){
		function sql($statment, &$o){
			static $connected=FALSE; // would be set to TRUE on successful connection

			if(!$connected){
				// get db connection data from config file
				@require(dirname(__FILE__)."/../config.php");

				/****** Connect to MySQL ******/
				if(!mysql_connect($dbServer, $dbUsername, $dbPassword)){
					echo "<div class=\"error\">Couldn't connect to MySQL at '$dbServer'.</div>";
					exit;
				}

				/****** Connection Charset ********/
				@mysql_query("SET NAMES 'latin1'");

				/****** Select DB ********/
				if(!mysql_select_db($dbDatabase)){
					echo "<div class=\"error\">Couldn't connect to the database '$dbDatabase'.</div>";
					exit;
				}

				$connected=TRUE;
			}

			if(!$result = @mysql_query($statment)){
				echo "An error occured while attempting to execute:<br /><pre>".htmlspecialchars($statment)."</pre><br />MySQL said:<br /><pre>".mysql_error()."</pre>";
				exit;
			}

			return $result;
		}
	}
	########################################################################

Was This Post Helpful? 0
  • +
  • -

#6 CTphpnwb   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3795
  • View blog
  • Posts: 13,738
  • Joined: 08-August 08

Re: PHP sql query. What's wrong?

Posted 10 October 2011 - 11:31 AM

It looks to me like this line:
            if(!$result = @mysql_query($statment)){


will always return false because you use @ to suppress errors.

This is an incredibly convoluted way of going about things. Why aren't you just using prepared statements?
Was This Post Helpful? 0
  • +
  • -

#7 AlwaysLearningABT   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 10-October 11

Re: PHP sql query. What's wrong?

Posted 10 October 2011 - 11:40 AM

Why would it always return false exactly? I guess I could just use a simple statement to get this done. i don't know really why this isn't working though. I like to be able to figure things out. Currently I am failing. lol. Thank you for any and all responses. If you think a simple SQL statement would do the trick in the original function UpdateEntranceDiaplay() then maybe I should go that way?
Was This Post Helpful? 0
  • +
  • -

#8 AlwaysLearningABT   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 10-October 11

Re: PHP sql query. What's wrong?

Posted 10 October 2011 - 11:55 AM

Might I ask how YOU would go about this? I am all for understanding things better and making it easier. A lot of this code is in place due to the admin panel in the backend, but doesn't necessarily need to be used for standard update functions.
Was This Post Helpful? 0
  • +
  • -

#9 CTphpnwb   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3795
  • View blog
  • Posts: 13,738
  • Joined: 08-August 08

Re: PHP sql query. What's wrong?

Posted 10 October 2011 - 12:00 PM

I can't check it right now, but I believe this would always return true, since you don't allow it to fail:
if($result = @mysql_query($statment)){


Placing a not before it (!) makes it false.

I would use PDO. See Dormilich's excellent tutorial on it here.

I'm having trouble seeing your overall logic. Maybe if you write it out in English we'll all get a better understanding of what you're trying to do.
Was This Post Helpful? 1
  • +
  • -

#10 AlwaysLearningABT   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 10-October 11

Re: PHP sql query. What's wrong?

Posted 10 October 2011 - 12:33 PM

Sure thing.


I have a table called Build_Entrance which is basically a bunch of values from about 12 other tables.

There are two fields that pull from a table called Entrance_Styles

Entrance_Styles has basically a Style name (such as P1 or F3) and a Graphic associated with it. (5675_879.jpg or something along those lines)

I want the field Ent_Display (From Build_Entrance table) to display the associated graphic from Entrance Styles (field ES_Graphic).
This should be dictated based on what the selection of the dropdown option Ent_EntranceStyle (From Build_Entrance table)
This field is populated with the name of the entrance style automatically from the table.

The reason I am putting it in the function Build_Entrance_after_update is so that it executes after the user clicks on save changes, save as new, or changes the selection of that field.

Please let me know if there is anything else you need to know. I have working site up with front end/back end currently. These are functionality changes I am working on.
Was This Post Helpful? 0
  • +
  • -

#11 AlwaysLearningABT   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 10-October 11

Re: PHP sql query. What's wrong?

Posted 10 October 2011 - 12:39 PM

Looks like the super quick error msg I get is in regards to this statment.

sql("update Build_Entrance set Ent_Display='$display' where Ent_Id='$entranceID'");

Was This Post Helpful? 0
  • +
  • -

#12 CTphpnwb   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3795
  • View blog
  • Posts: 13,738
  • Joined: 08-August 08

Re: PHP sql query. What's wrong?

Posted 10 October 2011 - 12:43 PM

View PostAlwaysLearningABT, on 10 October 2011 - 03:33 PM, said:

The reason I am putting it in the function Build_Entrance_after_update is so that it executes after the user clicks on save changes, save as new, or changes the selection of that field.

I don't have time to go over everything right now. Hopefully others will chime in.

I just wanted to point out that PHP is server side. That means that there is no way for the user to click while it is running. Things always happen in this order:
  • User takes an action.
  • Browser sends request to server.
  • Server runs PHP script.
  • PHP output is sent from server to browser.
  • Browser renders output.
  • See step 1.

Ajax may make it appear that things occur in some other order, but they don't.
Was This Post Helpful? 0
  • +
  • -

#13 AlwaysLearningABT   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 10-October 11

Re: PHP sql query. What's wrong?

Posted 10 October 2011 - 12:57 PM

I get that. I am really just asking for help on the sql statement. There has got to be a simpler way, correct?

	function updateEntranceDisplay($entranceID){
		// update entrance display picture
		$entranceID=makeSafe($entranceID);
		$style=sqlValue("select Ent_Style from Build_Entrance where Ent_Id='$entranceID'");
		$display=sqlValue("select ES_Graphic from Entrance_Styles where ES_Name='$style'");		
		sql("update Build_Entrance set Ent_Display='$display' where Ent_Id='$entranceID'");
	}


I think what I am missing is the selected.field. Instead of just using the value from the field I should update with the current selected choice.
Was This Post Helpful? 0
  • +
  • -

#14 CTphpnwb   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3795
  • View blog
  • Posts: 13,738
  • Joined: 08-August 08

Re: PHP sql query. What's wrong?

Posted 10 October 2011 - 01:19 PM

Using PDO and off the top of my head, I think I'd do queries something like:
SELECT Entrance_Styles.ES_Graphic WHERE Entrance_Styles.ES_Name = Build_Entrance.Ent_Style AND Build_Entrance.Ent_Id = ?

UPDATE Build_Entrance SET Ent_Display = ? WHERE Ent_Id = ?

This post has been edited by CTphpnwb: 10 October 2011 - 01:19 PM

Was This Post Helpful? 1
  • +
  • -

#15 AlwaysLearningABT   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 10-October 11

Re: PHP sql query. What's wrong?

Posted 12 October 2011 - 09:49 PM

View PostCTphpnwb, on 10 October 2011 - 01:19 PM, said:

Using PDO and off the top of my head, I think I'd do queries something like:
SELECT Entrance_Styles.ES_Graphic WHERE Entrance_Styles.ES_Name = Build_Entrance.Ent_Style AND Build_Entrance.Ent_Id = ?

UPDATE Build_Entrance SET Ent_Display = ? WHERE Ent_Id = ?

Thank you. That would have worked as well. However, My original coding was correct all along, the problem was I was returning comparing the returned value from &style with the wrong field as it should hace been Ent_Id not Ent_Name.

I used a MsgBox Function to display what each variable was after code run. I noticed the gap between the two sql queries. All is functioning just fine. I thank you for all your help.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1