?
Solved

Calling MySQL Fields in PHP

Posted on 2013-01-04
12
Medium Priority
?
296 Views
Last Modified: 2013-01-08
Hello,

I'm very new to PHP and MySQL.  I'm trying to troubleshoot a problem on my company's website, where certain fields that should show up are not.  The address doesn't show up, but the city and state do.

In looking at the code, I see where the fields are being called, but none of the field names actually match the field names in the database itself, even those fields which are displaying correctly.

Is there some sort of field-name-aliasing in PHP that I'm missing here?
0
Comment
Question by:Brodie Krause
  • 3
  • 3
  • 2
  • +3
12 Comments
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 38744511
AFAIK, there is no such a system. Maybe the field names are wrapped in constants so you should find somewhere something like define(USEDFIELDNAME, 'databaseFieldName');
but this doesn't seem to be a good practice, nor so usual. At the end of the day, I think this is very strange and if you could post here some code maybe I can help you better...

Cheers
0
 
LVL 10

Assisted Solution

by:jagadeesh_motamarri
jagadeesh_motamarri earned 600 total points
ID: 38744517
In a simple way you would access MYSQL using PHP as follows -

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

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }

Open in new window


But it totally depends if your application is using any php-mysql in-house developed wrapper. But somewhere in the framework you should be seeing the table and the columns that are being fetch as part of the select.
0
 

Author Comment

by:Brodie Krause
ID: 38744650
jagadeesh_motamarri, thanks!  Your sample code pointed me to the intial query for the MySQL db.

Here's the query:
$sql = "SELECT UID AS id, FirstName AS first, LastName AS last,  Location AS loc1, CONCAT(Address,Address2) AS add1, City AS city1, State AS state1, Postal AS zip1, Phone AS phone1, 2Location AS loc2, CONCAT(2Address,2Address2) AS add2, 2City AS city2, 2State AS state2, 2Postal AS zip2, 2Phone as phone2 FROM person ";

Open in new window


It seems this "AS" modifier is translating from the actual DB field name to what the developer wrote the code to call.  However, here's the section of code I'm looking at:
<?php
if($person['Location1'] != '') 
        { 
        $locfields = array('Location','add','city','state','zip','phone');
	for($i=1;$i<=4;$i++) 
                {
		foreach($locfields as $field) 
                        { 
			if(isset($person[$field.$i]) && $person[$field.$i] != '') 
                                { 
				echo $person[$field.$i]."\n<br>\n";
			        }
		        }
	echo "\n<br>\n";
	        }
        }
?>

Open in new window


So strangely, the names being called here don't even match what is being defined in that Query (they lack the numerical identifier).  But, in this case, the Zip code is displaying properly.  If I remove this zip reference, the zip code disappears off the served up page, so I know I'm looking in the right spot, I just fail to see the connection between these references and the db.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 10

Expert Comment

by:jagadeesh_motamarri
ID: 38744747
This seems to be the right code.

Observe the variable i in the for loop -

for($i=1;$i<=4;$i++) 

Open in new window


and this is what is being concatenated with the fields in the below line -

if(isset($person[$field.$i]) && $person[$field.$i] != '')

Open in new window


so each field is accessed as $field.$i
0
 
LVL 5

Expert Comment

by:Goofytouy
ID: 38744809
Hi... I maybe wrong but... I do not see at any place, how is the $SQL executed, in other words the call of function mysql_query....

That function loads the corresponding resource where information it's going to be saved...
Usually, information is loaded into an array with something as

$fields = mysql_query($Query1) or die ("Database error");

Open in new window


afterwards, you retrieve each row data using the function mysql_fetch_array

$row=mysql_fetch_array($fields)

Open in new window


You should check for some variation of that.


Regards
Goofy
0
 
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 200 total points
ID: 38745425
I think your developer is trying to show how clever he is by renaming everything and making it hard to maintain.  I'm currently working on a site with similar problems where the developer made things clever instead of maintainable and it takes forever to figure out how to make changes that don't break the site.
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1200 total points
ID: 38746593
@DaveBaldwin: Ever notice how the Venn diagram of "clever" seems to overlap "stupid"? ;-)

@BKCK2k3:  Drop what you're doing.  Buy this book right now and give yourself the time necessary for you to read it and work through the examples.  It is an excellent introduction to the essentials that you need to know.  Without a foundation in PHP and MySQL you might as well be trying to write German poetry without understanding German vocabulary and grammar!
http://www.sitepoint.com/books/phpmysql5/

That said, and in respect of the learning curve ahead of you, you might want to consider hiring a professional PHP developer rather than taking the time to learn PHP (and making all the mistakes along the way).  From what I can see in the little bit of code you've posted here, the existing codebase realizes a technically incompetent design and it will be very hard to add to it successfully.  It may be a faster path to success to discard this code and start over with the right processes.

But if you want to do this yourself, here are some things you can do that will help.

First, understand that it's all about the data.  The only thing computer programs do is transform data from one format to another format.  So naturally you want to visualize the data at every turn in order to see what the programming is doing to it.  This PHP function is your friend: var_dump().

You want to do something like this (probably right now) to see what's retrieved by the query... (and you're right about the use of the AS modifier).

$sql 
= 
"
SELECT 
  UID AS id
, FirstName AS first
, LastName AS last
, Location AS loc1
, CONCAT(Address,Address2) AS add1
, City AS city1
, State AS state1
, Postal AS zip1
, Phone AS phone1
, 2Location AS loc2
, CONCAT(2Address,2Address2) AS add2
, 2City AS city2
, 2State AS state2
, 2Postal AS zip2
, 2Phone as phone2 
FROM person 
"
;
$res = mysql_query($sql) or die("FAIL: $sql WHY: " . mysql_error());
echo '<pre>';
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}

Open in new window

Your data dump will be much easier to read if your script uses echo '<pre>' to set the formatting.  Once you see the data coming back from the query, you can begin to figure out how to display this data predictably.

Empty columns in the query results set are still present and are defined data elements.  You don't have to do anything special with them - just echo them and PHP will send an empty string to the browser.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 38747176
Ever notice how the Venn diagram of "clever" seems to overlap "stupid"? ;-)

Oh yes.   I guess I shouldn't complain.  These events make money for you and me and Cd&.  It seems that the clever ones think no one else will be able to maintain the site that way.

So boys and girls, just keep on with the clever coding.  When it stops working, they will hire one of us older coders to fix it.  Unless it's a Smarty template or Xoops.  Those are real close to being cleverly Wrong even when the are right.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 38747343
@DaveBaldwin: Right On!
0
 

Author Comment

by:Brodie Krause
ID: 38751875
@DaveBaldwin:   I had a feeling that was the case, regarding the clever developer, but lacked the foundational knowledge to throw that stone!  From the little bit I've gleaned from the code, my reaction has consistently been, "It's got to be simpler than that!"  After all, why would anyone rely so heavily on PHP if it is really this cumbersome!  :-)  I just stepped into this job a couple months ago, and when the issues with this site were brought to my attention, I was told the developer wanted an additional $3k to fix the problems, or $5k to rewrite the site "using updated standards and practices".   Needless to say, I'm now very motivated to learn this stuff to break our dependence on guys like this.

@Ray_Paseur:   Thanks for the tips and the resource, I've just placed the order and will be checking it out ASAP.   From what I can tell, you're dead on that the site was not written using best practices.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 38751963
Clever code and incompetent code are the two things that keep me in business.  There are some things I won't work on like Smarty and Xoops because they are frameworks that are too complicated to begin with.  But most things can done fairly simply even with a pretty fair amount of detail.  Good luck on your project.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 38754392
Thanks for the points, and best of luck with it! ~Ray
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month17 days, 5 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question