Displaying specific table cells from MYSQL database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Displaying specific table cells from MYSQL database

Post by mikosiko »

if i specify the "email" row as the primary key, would i call the row by the email value?

I also dont know how to specify the values.
Im reading up, but i cant find any examples of how im trying to do things
well... you have definively more problems that just simply write some code... you should start reading about relational database modeling and design first.

explain what exactly are you trying to do (in clear details) and probably someone could offer some help and examples
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Displaying specific table cells from MYSQL database

Post by califdon »

You would really benefit from reading a tutorial on relational databases. There are some crucial concepts and understandings that are not intuitive, but vitally necessary in order to get results from a database. Try using the keywords database relational fundamentals in Google or Bing or Yahoo! I even wrote one myself a few years ago: http://forums.aspfree.com/microsoft-acc ... 08217.html (it was written for Microsoft Access, but it applies totally to any relational database).

When you describe a database table, the best way is not to show data, as you have done, but to show the structure, like this:
[text]table name:
column1name INT (Primary key)
column2name VARCHAR
column3name DATE
column4name VARCHAR
. . .
[/text]
And nearly every functional table needs a primary key. You choose the primary key as whatever field (or fields) will logically and uniquely identify a row. It is, by definition, unique, that is, there can be no possibility of 2 rows having the exact same value for the primary key column. Or if there is no such "natural" key column, you ADD an arbitrary value column that becomes the primary key. You can designate it as an auto-increment column that the database will assign a value to for every new record, guaranteeing that it will be unique. This is a very common practice.

Another database design rule is that every column in a table (roughly what you think of as a "cell") should be "atomic", meaning that it must contain a single value, not a combination of values. Although it is possible to work with just part of a value, it is highly inefficient and should be avoided. I'm referring to your column of email addresses, where you seemed to expect to identify which row you wanted by just the username part of the address.

Yet another design rule is that, generally, you should not store "calculated values". Here I'm talking about your "month to day" column. If I am interpreting your meaning correctly, you are planning to have a database that stores the number of clicks each email address accumulates. Your database design won't work for that purpose. You should be recording each click with the exact date-time stamp and email address as a row in the table. To show either the total number of clicks, or the number of clicks since the first of the current month, or the total clicks last year, etc. etc., you simply use a SELECT QUERY and you will be able to provide those results. This is what makes relational databases so powerful.

In every case when you want to retrieve stored data, it is done with a SELECT QUERY, which selects the data from one or more rows, and very often from related data in other tables. So the very first thing you must learn in working with a relational database is the SQL language, which is what your PHP script sends to the database to retrieve the data you want. Back to Google, search for sql tutorial.

In closing, let me emphasize that relational database design and use is NOT INTUITIVE! It is an entirely different way to think of data than you have ever encountered. It is nothing remotely like a spreadsheet. If you're going to learn how to use them, you really must start at the beginning, not by copying a part of some script.
The450Man
Forum Newbie
Posts: 14
Joined: Sat May 19, 2012 9:40 pm

Re: Displaying specific table cells from MYSQL database

Post by The450Man »

Thanks guys, its really appreciated.

Let me give you all a background on what im trying to do. Im fresh out of college and im giving this a shot to pay off my student debt.

Im in the process of creating my own PPC/PPS (paid per click/paid per sale) network.

I dont have a large sum of startup funds so i have to do what i can.

I pay websites for each click or sale they generate through my adds.

Im using an opensource tracking software, its called propser202. A server script. It stores its information in a database just like many of you describe. Sadly, I really dont know how to implement this as creating actual values from a database that contains many tables linked together; they contain many different values, it is quote overwhelming and confusing confusing.

The tracking software has an admin login pannel and thats it. I can log in and see the statistics for EACH of the campaigns. This is great for me, but my clients cant track their statistics unless i give them my admin credentials giving them access to everything; needless to say i dont want that.

Since i dont know php, i figured id create a "ubot" (software to automate website manipulation). Id simply log into my admin control pannel, scrape the contents of the campaigns; save the campaigns in a crude format in a mysql database. From there i could simply create a webpage for the client to navigate to, displaying the statistics for their campaign.

Now the simple way would be to use the code from the open source tracking software and integrate it into a separate webpage using the mysql database that the tracking software uses. The creator of the software charges a boat load of money per the hour and im broke so that is out of the question.

If someone is willing to do a form of a Joint Venture that would be great. My expertise is in advertising and marketing, not scripting.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Displaying specific table cells from MYSQL database

Post by califdon »

That's certainly understandable, and I don't envy you trying to get started with the burden of student loans! I'm about to leave for a meeting, but I'll try to give your situation more attention later today or tomorrow. In the meantime, let me quickly give you some advice: for a serious business purpose, DIY programming is very risky. I would be very careful about either modifying an open source package yourself, or even attempting to develop your own software. These are pretty complex systems, even for an experienced programmer. In a beginner's hands, it could easily result in a complete disaster for a struggling, new-born enterprise. I'll try to think of some options later today.
The450Man
Forum Newbie
Posts: 14
Joined: Sat May 19, 2012 9:40 pm

Re: Displaying specific table cells from MYSQL database

Post by The450Man »

Thanks.

Yea, i wanst thinking of modifying the actual script. I was thinking of using a segment of the php script that displays the stats of the actual script and use that to create my own webpage that displays just that single campaign that i specify.

That way i wont have to create my own mysql database and just read what is already in the original database that prosper202 uses to store information.

Yea, any help is greatly appreciated.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Displaying specific table cells from MYSQL database

Post by califdon »

The450Man wrote:Thanks.

Yea, i wanst thinking of modifying the actual script. I was thinking of using a segment of the php script that displays the stats of the actual script and use that to create my own webpage that displays just that single campaign that i specify.

That way i wont have to create my own mysql database and just read what is already in the original database that prosper202 uses to store information.

Yea, any help is greatly appreciated.
Disclaimer: I had never heard of prosper202 (but have now spent a few minutes looking at their website) and I have no background in PPC/PPS network marketing.

That said, my strong feeling is that trying to use part of their script is likely to be futile, especially for a non-PHP programmer. There are countless dependencies that even an experienced programmer would have to delve into in order to use just a particular part of it. My inclination would be to determine their database schema (structure)--easy for me, maybe not so much for you--and evaluate whether you can get what you want from their database, then write a simple PHP script to extract that data in the format you want and either display it or create a csv (comma separated values) file that you could work with in Excel. I say "simple" because it would not be difficult for someone who has at least some experience with PHP and MySQL. But if you are only barely beginning to learn PHP and with no database experience, I honestly think it's not worth the substantial time and effort I guarantee it will require if you attempt this on your own.

So if you don't do it yourself, who could do it for you, or with you? If you could pay someone several hundred dollars, you might find someone on these forums who could help you, but even if you could afford that, you would need to have some confidence in their ability. I'd like to think that everyone here is both competent and honest, but there's no way to guarantee that. Ideally, if you could find someone in a situation not unlike yours, perhaps a recent graduate still struggling to gain some experience, only their skill set would have to include PHP and MySQL, maybe someone would be willing to join forces, either for a promise of a future stake in your operation, or even just for the experience. But again, finding the right person may be difficult. Maybe if you could find someone in your geographic area who could meet with you and work with you in person, that would be the best solution, but depending on where you live, that may not be easy either.

Certainly I'm viewing the task with a lot of negativity, but I sincerely believe that if you pursue what is a challenging task for an experienced database developer, without the requisite knowledge and experience, the result will be months of thrashing around with technology in which you aren't experienced, while you're not using the marketing skills that you've been studying for all this time, probably ending with a web application that either doesn't work at all, or works unreliably--which could cripple your new venture!--or is vulnerable to security breaches. Those kind of results could kill an otherwise promising new enterprise, which I'd hate to see happen.

So, bottom line: my advice is to seek an unpaid partner, local if at all possible, to handle that part of the business, or if you can possibly afford to hire someone with the skills and proven track record, consider it as a necessary investment in your new business enterprise. I wish you the best of luck.
The450Man
Forum Newbie
Posts: 14
Joined: Sat May 19, 2012 9:40 pm

Re: Displaying specific table cells from MYSQL database

Post by The450Man »

thanks for the reply califdon.

It really seems the most logical way for me to pursue this would be to find someone who would be willing to go on a joint venture with me.

The database scheme is quite interesting.

One database is called clicks, it contains all the clicks. Specific columns state the time, which campaign it belongs to and more. It is definitely over my head.

So im now searching for someone who can help me, any takers?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Displaying specific table cells from MYSQL database

Post by califdon »

The450Man wrote:The database scheme is quite interesting.

One database is called clicks, it contains all the clicks. Specific columns state the time, which campaign it belongs to and more. It is definitely over my head.
Right. That's exactly what I would expect (and what I described in my earlier post: "You should be recording each click with the exact date-time stamp and email address as a row in the table."). This is the core concept (that I used to teach for many years), that a relational database stores raw DATA, not processed or calculated data, and the database engine (MySQL or Oracle or Microsoft SQL Server, whatever) does all the selecting, sorting, combining, comparing, calculating, based on the SQL language.
The450Man wrote:So im now searching for someone who can help me, any take+s?
You may want to post in one of our other forums here, like Volunteer Work (click on Board Index at the top of this page). Best of luck.
The450Man
Forum Newbie
Posts: 14
Joined: Sat May 19, 2012 9:40 pm

Re: Displaying specific table cells from MYSQL database

Post by The450Man »

Thanks man, I will do so.
Post Reply