Need help using PHP array to fetch data from MySQL

chekov

Hi all,

I just got - as many others - such a newbie question I seem cannot get over or being able to solve it by myself.

So any further ado, let's go:

I have stored lots of info regarding technical, computing and programming books into MySQL database.

I am able to fetch infos from MySQL database by using PHP just fine. I've done very primitive frontend that suits my needs. It prints out infos regarding book author, title, isbn, year when published and so on and so on.

I've also stored Chapter index of every book and now comes the tricky part:

I'm able to print out also chapter index but not really the way I want; in MySQL table, there's 150 fields which all can be used to store chapter info.

I've created up to 150 fields in the database table as I believe it's sufficient for my needs.

Everything's still fine but as some books got smaller chapter index, let's say, 48 entries and some books may have chapter index over 100 entries.

I fetch all the data from the database table (150 fields) and I've genereated an array which goes until the end of the table entries and generates the list from that amount.

Everything has been printed out but for the book with only 48 chapter index entries, there's additional 102 EMPTY entries following the first 48.

So basically for every book I've got stored in MySQL database,
my PHP script prints out the complete 150 entries regardless amount of entered real chapter data into database.

I want to limit the entries to be printed out to the amount of real data entered in MySQL database. So if there's 48 entries of chapter index, there should be printed out only those 48 entries, instead of 150 entries, the size of my database table total fields.

I would really appreciate a lot if someone would come up with hints how to get further. I feel like I'm stuck in the middle and cannot get this done the way I want.

Thanks a lot in advance. Sorry if this post appeared in a wrong forum.

bastien

change the query


select * from mytable where book = somthing and chapters_index is not null

assuming the elements are consecutively placed in the db (1,2,3,4 and not 1,3,4,5,7) then you can simply check for a null
```
if (is_null($rows['chapter_idx'])){
   //exit loop - depends on loop type
}
```
hth

chekov

Thanks for your reply hth, I found it partly helpful as I had forgotten to use 'IS NOT NULL' in the db query.

Anyways ... I'm still with the problem as I need to print out the result entries from the db into html.

I'm still having a numbered list of these chapter index details from db and there are 150 fields.

These chapter index details will be printed in html as a numbered list: <ol> ... <li> <li> <li> ... </ol>

So in between <ol> and </ol> tags in PHP file I'm using the following code:

	for ($i = 0; $i < mysql_num_fields($result2); $i++) {
		echo("\t<LI>" . $row2[$i] . "\n");
	}

That way I can generate the list as it loops the result set from my db query. But as there's 150 fields in the db table, I'm having for every book such a huge numbered list until 150.

So I want to print out only the entries which have some meaningful data in it (in my case text type of data regarding chapter indexes of books) so that I get clean numbered list which runs until the end of actual chapter index of a book, not all 150 fields.

Thanks again for the help.

bastien

On further reflection, I think the db design needs to change...the design with 150 cols just doesn't make sense from a db perspective

A better table layout would be

record_id (pk autonumber)
book_id (id from books table)
chapter_id (the chapter)
chapter_indx
chapter_summary

This way there are never unfilled entries in the db. The record set returns the right results and the loop is way easier to program as mysql_fetch_arrays($result) works well with the data set

chekov

Thanks again Bastien for your help.

I was thinking what you mentioned on your mail that it indeed might make more sense to consider re-design the db layout and then proceed further.

On the other hand, still some additional things regarding my problem:

If I would have anything in between 1-3 sheets of cards from a special register and there's written data on both sides of a card.

In the database it would go like this:

SHEET 1 (a stands for front side and b for backside)
a1 ... a20
b1 ... b20

SHEET 2
c1 ... c20
d1 ... d20

SHEET 3
e1 ... e20
f1 ... f20

So my database table looks like:

sheet_id
a1
a2
a3
a4
a5
..
a20
b1
b2
..
b20

...and the list goes until f20.

Some sheets and either side of a sheet will reach the maximum 20 values but some sheets not.

How can I print out the result set so that it won't print out empty cols until 20 if the side of my sheet reaches only, let's say, 11 entries ..?

Thanks again for your help!

bastien

Sam design principles can be applied here.

Table

record_id (pk autnumber int)
sheet_id
element_id
element_value

element_id the front or back of the sheet (can keep as a or b for all sheets, since each is separated by the sheet_id)

element_value is the value of that item

Again then its the sam programmin structure to pull out the elements and a

while $rows=mysql_fetch_array($result)

will handle the rows output