Displaying data from 2 or more tables.

[CB150Special]

I have a member table and a list of transactions in another table linked by MemID.

I'm trying to display the member details and a list of transactions.

 

I was hoping to do 2 different sql queries and then display the results, but, it looks like PHP gets the data with the same $row function.

 

Does this mean I have to get the one tables data, display it, and, the get the next tables data and then display its data ?  

 

 

 

[requinix]

Post your code and table structures.

[mac_gyver]

when dealing with related data, between multiple or the same table(s), your goal should be to write a single sql query that gets the data you want in the order that you want it. you do this by writing a JOIN query. there are several types of joins and you can research on the web or in the mysql database documentation to find out more information about them.

 

in order to separate the database specific code, that knows how to query for and retrieve the data, from the presentation code, that knows how to produce the output from the data, you should fetch the data from any query into a php variable named to indicate the meaning of the data. use a scalar variable for a single value, a one-dimensional array for a result set consisting of at most one row, and an array of rows for a result set consisting of one or more rows. by separating the different concerns in your code, it will be easier to define, write, test, and debug your code.

 

 I was hoping to do 2 different sql queries and then display the results, but, it looks like PHP gets the data with the same $row function.

 

 

$row is a variable and you can name them anything you want, to identify the meaning of the data in them. if you were querying for completely separate and unrelated meaning data, you would fetch the final data into differently named variables.

 

with a JOIN query, the common data is repeated in each row in the result set. to get one instance of the common data (member details in your example), you can just reference the zero'th entry in the php array variable holding the data. to output the dynamic data (transactions in your example), just loop over the php array variable holding the data.

[CB150Special]

Thanks for the info. I thought $row was some sort of reserved word as it is not defined anywhere in my code. It appears that PHP creates variables on the fly.

 

The association of $row and the data is with the statement, while($row = mysqli_fetch_array($result)); 

 

I didn't pick that up till I read your reply. Funny how every example I looked at used $row.

So

 while($row1 = mysqli_fetch_array($result1)); 
 while($row2 = mysqli_fetch_array($result2)); 

are perfectly valid lines and helps me do my code. 

Edited July 16, 2017 by CB150Special

[Jacques1]

No, those lines are not “perfectly valid”. You've clearly missed all the relevant parts.

 

The most important message is that using nested loops to select related data from multiple tables is plain wrong. It's horrendeously inefficient and cumbersome, and you're reinventing features which already exist in the database system itself. As mac_gyver already explained, you need to use joins. Joins are a core concept of relational databases, so if you didn't know them yet, learn them now – actually, learn all the basics before you start writing code.

 

Also, stop using meaningless variable names like $row or $result and then appending numbers to work around name collisions. Use proper names which actually describe the content like $member_details or $member_details_and_transactions. This has a direct effect on the number of bugs, not only because it eliminates name collisions but because it makes the code readable.

[CB150Special]

Hi Guru. You appear to be quite aggressive in you replies. You also started programming at some point. Nearly every book and even w3schools uses $row and $result. If it is good enough for them, then it is good enough for me at the moment. In time, I will learn and improve.

 

PS you talk about PDO in another one of my posts. I must use it.  Well, after quite a bit of reading unless I'm using multiple databases, there appears to be be no major advantage.

 

I think help people to go forward and try answer their question however stupid they may sound and keep your personal opinions to yourself unless asked. 

 

Encouragement is the correct way to go forward.

Edited July 16, 2017 by CB150Special

[Jacques1]

It's always fascinating when somebody admits that he doesn't know anything about PHP, yet at the same time thinks he's competent enough to know exactly what is “good enough” and which parts of PHP he doesn't need. PDO is for “multiple databases”? What on earth are you talking about?

 

Not knowing is fine. Not wanting to know is a real problem, especially for a (would-be) programmer. You've already reached the point where you spend more time making up excuses for problems than simply solving those problems.

 

Encouragement is great, but I'm old enough to know the difference between somebody who actually needs more time and somebody who just wants to avoid work. I've seen people play this it's-good-enough-for-me game for 10 years and longer, and they always have new excuses for why they still haven't made any progress. What they need is a dope slap, not another 10 years of “encouragement”.

 

This is programming. We're dealing with objective truths, so the you-hurt-my-feewings-safe-space bullshit doesn't work here. When code is wrong, I point that out, and I expect the same from overybody else. If w3schools says your code is “good enough”, they're lying to you.

Edited July 16, 2017 by Jacques1

[mac_gyver]

Thanks for the info. I thought $row was some sort of reserved word as it is not defined anywhere in my code. It appears that PHP creates variables on the fly.

 

 

php creates variables upon assignment of a value to them. outside of class properties, there is no specific variable declaration statement, and even there if you don't define them, php creates them upon assignment (may be version specific.)

 

So

 while($row1 = mysqli_fetch_array($result1)); 

 while($row2 = mysqli_fetch_array($result2)); 

 

 

are perfectly valid lines and helps me do my code. 

 

 

the lines of code are valid, and i'm pretty sure just examples of two different lines of code and not actual nested loops you intend to use.

 

however, in general, you should try to program in general  . program logic, that is performing the same steps each time you use it, such as assigning the sql query statement to a variable, executing the query, and fetching the data from the query, should be the same set of statements and (re)use the same variable names in the same place, except for the final variable you are assigning the data to. this will help reduce typo errors and will lead you to realize that you should create functions/class-methods with this common logic in them, to call, instead of actually repeating the same logic each time you need it.

 

the numbered $resultX and $rowX are not needed and just make more work for you in keeping track of what variables are being used where. using the recommendation of separating the different concerns in your code, you would form the sql statement, execute it, and fetch the data from it before moving onto the next query your application needs.

 

the php PDO extension is simpler and more constant than the php mysqli extension. for common activities, mysqli requires more statements than PDO. with PDO, no matter how you execute a query, you will always deal with a PDOStatement object. mysqli has a bunch of different types of query results, depending on the type of query and how you execute it, and one of them is dependent on php build and underlying driver availability. the default fetch mode even changes between the different types of mysqli results. the biggest differences in using mysqli and PDO are when dealing with prepared queries, which is what you should be using when supplying data values to the sql query statement. the mysqli prepared query programming interface is a joke to write code for and doesn't even come close to how easy PDO is to use.

Edited July 16, 2017 by mac_gyver

[Jacques1]

the mysqli prepared query programming interface is a joke to write code for and doesn't even come close to how easy PDO is to use.

 

Oh my gawd, you programmers are so negative. Can't you just say that mysqli is differently abled? Or interfacially challenged?

[gizmola]

Hello CB150Special!

 

You should be able to see that Jacques1 has nearly 4000 posts here. Just about every post he's made is in response to a question like yours.

 

Sometimes he's terse. Sometimes he's abrupt. At times he's a bit rude, but at the end of the day, he's sharing his expertise with others, and has accumulated 573 likes in the process. Those likes have been accumulated because people appreciate the information he's disseminating.

 

As for mysqli or PDO. You're free to use whichever one you wish. The api is sucky and hard to use when compared to PDO. That is the opinion of the vast number of professional php programmers who have used both. mac_gyver wrote you a whole paragraph on the differences between the two.

 

You were also offered the advice that it is always better to do in 1 query what you might otherwise do in 2 -- If you can. That is just a best practice.

 

Of course database access in php is only as useful and productive for you as your understanding of the database and its syntax is. If you don't understand SQL and relational design, then I can understand you might struggle and avoid joining tables together. Just understand that people are going to tell you that you are doing it wrong, even though you can end up with the same output in the end.

 

Last but not least you offered this code:

 

 while($row1 = mysqli_fetch_array($result1)); 

That is simply dumb code, no matter where you got it from, and here is why:

 

Let's say you have a color table with 3 rows in it (1, 'red'), (2, 'green'), (3, 'blue').

 

Your query is this: "SELECT * FROM color";

 

When the query runs, you expect that the result set on the server is going to have 3 rows in it.

 

 

Now we apply your code:

 

while($row1 = mysqli_fetch_array($result1)); 

So what does $row1 have in it? Is it 1 row, 3 rows? If I do this:

 

echo $row['name'];

What should I have in there?

 

If your expectation is that you will only have one row in the result set, what point is there in having a while() control structure? How is that good/smart code?

 

If however, I want to store all the rows in the result set, that code is useless and dumb.

 

I created this eval for you to help you understand what is wrong with the code snippet you provided.

[CB150Special]

Quite a difference in replies between posters.

I fully understand variable names and how to give them meaningful names.  My issue and I suspect others as well coming from other environments is that you normally have to declare variables before you use them. As a new PHP programmer we have to learn to observe these differences in languages. Sometimes, as in this case, I didn't know which was a variable and which was a reserved word.

 

As every source I read on SQL bits used $rows, I thought it was a reserved word.   I'm now the wiser.

 

One of the difficulties with HTML CSS PHP etc is the 'lack' of a standard with suitable documentation.  Back in the old days, yes, Fortran, that is how far I go back, there was one manual. You read the manual and you were are away.  Then came along Dbase, SQL etc and each and every time, the parameters became looser and looser. Now if you can suggest ONE definitive book on CSS, HTML, SQL and PHP that covers it all, I'll rest my case.

 

Anyway I'm making much progress with the help of so many postings on the web and your help. My website is nearly up and running, I'm currently looking the differences between Mobile and Desktop options to make sure it all looks ok on both platforms.

 

Have a great day.

[Jacques1]

Quite a difference in replies between posters.

 

All replies said the exact same thing, so when you jump from total rejection to complete acceptance, this is mostly a matter of your attitude.

 

Just be warned that there's a thin line between a diva and an idiot. You obviously won't get this amount of attention in every thread, so at some point, people will just assume you're unable or unwilling to learn when you don't respond to information.

 

Like I said, this is a programming forum. The focus is on raw technical advice.

 

 

 

One of the difficulties with HTML CSS PHP etc is the 'lack' of a standard with suitable documentation.

 

All those languages are extensively documented. There's the PHP manual, and the W3C maintains both the HTML and CSS standards.

 

On top of those primary sources, there are platforms like the Mozilla Developer Network which focus on making those information digestible for a wider audience.

 

Note, however, that there's also a lot of misinformation out there. A good example is w3schools (which you cited earlier). They have an officially sounding name and a fancy website, but large parts of the content is either misleading or plain nonsense. Tutorials from amateurs also tend to be abysmal.

 

 

 

Now if you can suggest ONE definitive book on CSS, HTML, SQL and PHP that covers it all, I'll rest my case.

 

This magical book doesn't exist, I'm afraid.

 

Web technologies are a moving target, so books can at best cover the basics. Even that rarely works out. The intersection between the people who know PHP and the people who write books seems to be very, very small.

 

The main source for web technologies is in fact the Internet: online manuals, forums, blogs, free source code.

 

Of course you can still use the (bad) books which are available as a starting point. I also did that. Just don't expect them to be the ultimate source of programming knowledge. A lot of what you'll read is going to be outdated, poorly written or factually wrong. But to be fair: This isn't just a PHP problem. I had the same experience with almost every language I learned.

[CB150Special]

To answer my original question.

 

$result and $row, which nearly every book and web links uses, are variables that  PHP creates on the fly. They are not reserved words.

 

You can do several SQL queries at any one time and, if you use meaningful words like $result_postcodes and $row_postcodes, it will make your subsequent coding much easier to understand. 

 

There are other useful opinions in the thread so it is worth reading, but the above answer was what I was looking for.  Probably one important thing - is don't always trust the web. There are many posters out there, but there is no real accurate feedback that checks what they have posted is correct.

 

Another thing for this forum posters is, read the question carefully, what is the underlying issue? Sometimes we are not exactly sure of what we want or we don't know how to express it.

 

In this case, my underlying issue was, reserved words and variables, nothing to do with SQL. If I knew the answer to that, I would have answered my own question. 

[Jacques1]

The thing about questions is that newbies don't see the full picture. That's not your fault, but you need to be aware of it to actually understand how (good) forums work and how to use them efficiently.

 

On the surface, yes, your question is about variables. We could have told you to simply use two different names, then you would probably have been very happy, given everybody a “like” and called it a day. Many forums work exactly like this, and newbies love them.

 

Unfortunately, this is nonsense. The real question is: Why did variable names suddenly become such a big problem? Not understanding the PHP syntax is definitely an issue you need to work on, but that's no explanation for why a simple variable name breaks your whole code. As it turns out, the only reason why you so desparately needed two different row variables is because you're running nested queries instead of proper joins. Otherwise you could have easily lived with your false idea that $row is a “reserved word”.

 

So whether you like it or not, this is all about SQL. Your “solution” is, at best, a bonus discovery. Actually, even the syntax problems are better solved by going back to the basics. Variables and keywords are two entirely different things in PHP, and you should know that “=” simply denotes an assignment.

 

I understand that digging deeper is hard and uncomfortable. While w3schools easily wins every popularity contest, some users think we (and other more professional communities) are all assholes who are too stupid to read and answer their questions. In reality, the quick, easy-to-digest answers are often nonsense. A good helper will always look at the problem behind the question, check if the approach chosen by the OP even makes sense and then give comprehensive advice. This may very well lead to an entirely different topic than the OP expected. If you want a good answer rather than a quick one, you have to be prepared for that.

 

If you “don't trust the web”, that's a problem for a web programmer. All primary sources and knowledgeable people are almost exclusively online. Of course that doesn't mean you should trust everybody (I think I made that clear in the previous reply). But if you expect PHP to work like FORTRAN with printed manuals that tell you what to do, you won't have much success. PHP is constantly evolving and requires constant learning. “Book X said it's OK” doesn't work.

Edited July 18, 2017 by Jacques1