Jump to content


Photo

PHP and mysql cells


  • Please log in to reply
3 replies to this topic

#1 Penny_Rollo

Penny_Rollo
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationEngland

Posted 02 October 2006 - 01:38 AM

Hello to everyone ..... realise it's early evening for most, but here in England it is the early hours of the morning and I guess this posting is, for me, a last ditch attempt to get things sorted :)

Most of this weekend has been spent grasping the basics of mysql and I have finally managed to get a database uploaded to my ISP's server.  Database is 34 columns across and 471 rows downwards.  The first column has 001, 002, 003 (etc.) downwards on the rows.  All fields are named (first one is just called "number").

I have managed to code in the php connection to the database to get the output at http://ccgi.rollobks...lishvocab11.php .

(using the following code)

<?php
$db = mysql_connect("hostname","username","password");
mysql_select_db('database_name', $db);
$SQL="SELECT * FROM tablename";
$RESULT=mysql_query($SQL);
$myrow=mysql_fetch_array($RESULT)
?>


I also previously managed to get the output at http://ccgi.rollobks...lishvocab02.php to register the number of rows (at the top, in black) using slightly different coding of

<?php
$link = mysql_connect("hostname","username","password");
$db_selected = mysql_select_db('database_name', $link);
$result = mysql_query("SELECT * FROM tablename", $link);
$num_rows = mysql_num_rows($result);

echo "$num_rows Rows\n";

?>


so I'm assuming that the polishvocab11.php page also has a working connection to the db (as no errors were generated when calling up the page).

I need help (please!) with three things.

Firstly how to get php to call up the content of one row at a time (so as to fill in the blanks for English 01, XXXXlang 01, etc. which are fields in the database, and where I understand "echo" commands are needed at the relevant points in the html template where currently there are %%xxxx%% entries.

Secondly how to then get php to call up the "next row" against the same html template (right through the 471 rows) so that each newly-generated page has the right content from the called-up line of the database.

Thirdly how to get php to give the output page a new URL with the format

<BASE HREF="http://www.happychil... no%%.htm">


so that I can store the 471 outputs as (separate) static html pages.

I'm not sure if this is something of a tall order! but it's what I need to achieve, and time is at something of a premium right now.

I also need to know how to call up a specific table cell (given the parameters of field name and the numerical entry in the first column, which seemed the most logical way to identify separate rows) in order to achieve output pages like http://www.happychil...useoverview.htm .

I'm involved in a (fairly huge) project to provide free dual-language worksheets for kids, for a number of languages including Romanian-English, Polish-English, Bulgarian-English, Russian-English etc., and several of the translated databases will be ready soon [all the translations are being done in similar formats].  The overview page content was achieved previously (for the English-French content) by using mail-merge on MS Works against an MS Works db (and then pasting each lot of table contents onto the page), but because of the length of the foreign-language-character entries (much of which have had to be converted into hex codes) the program truncates them, so I need to find a way of calling up a single cell at a time, to get the full text, but by a more automated process.

Logic tells me it must be possible!  but I simply can't get the content of individual cells in the database to be called up by the PHP code to appear on the page.  If any of you PHP experts could help I would very much appreciate this :)  Project HappyChild is "not for profit" and run very much on a shoestring, so there's no "profit motive" in doing this, but it will make a lot of difference for kids in various countries.

And I will be slightly saner if I can find a workaround for this - have tried out *loads* of PHP text copied laboriously from lots of tutorials but don't have the time to give to endlessly experimenting when I seem to be getting absolutely nowhere :/

Sorry this first post is so very long but I figured it might help if I explained something about why this needs to be done and exactly what help is needed, if anyone could explain what I need to do.  I'm not the most "technically-minded" of people! but I do grasp things *eventually* :)

Regards,

Penny.
Penny Rollo

usually at http://www.happychild.org.uk

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,022 posts

Posted 02 October 2006 - 09:14 AM

I found this a great help when I was just starting

http://www.sitepoint...-mysql-data-web
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 steveclondon

steveclondon
  • Members
  • PipPipPip
  • Advanced Member
  • 161 posts

Posted 02 October 2006 - 09:44 AM

Hi, you need mod rewrite to write your pages from static to your php pages. This uses reg expressions and redirects to which ever script your set in your .htaccess. Contact your administrator and ask if you have access to this. I just skated through your post (it was like a book), am waiting for part two to come out. I see that barand has given a link to a site that will give some help regarding alot of this. If you are still stuck come back and let us know. P.S. Not everyone that answers is in another country some of us are in the UK.

#4 Penny_Rollo

Penny_Rollo
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationEngland

Posted 02 October 2006 - 11:06 PM

Hi, you need mod rewrite to write your pages from static to your php pages. This uses reg expressions and redirects to which ever script your set in your .htaccess. Contact your administrator and ask if you have access to this. I just skated through your post (it was like a book), am waiting for part two to come out. I see that barand has given a link to a site that will give some help regarding alot of this. If you are still stuck come back and let us know. P.S. Not everyone that answers is in another country some of us are in the UK.


Thanks Steve and Barand, appreciate the replies.

Some long time after posting here, I solved the first question, that of calling up a specific row, and then calling off values within the page.  Code used was:

<?php
$db = mysql_connect("hostname","username","password");
mysql_select_db('database_name', $db);
$SQL="SELECT * FROM tablename";
$result = mysql_query("Select * from tablename where number='004'");
$data = mysql_fetch_array($result);
?>


I've left in the "number" (being the field name of my first column) and "004" (being the entry in the nominated row of the "number" column) so it might be clearer for other people to see what worked for this.

All I did to call up the values from the specified row was to put

<? echo $data["categorywspaces"] ?>


(or whatever the column name was, within the bracketed-area) wherever I needed to have a value from that row appear in the document.  Worked for the meta-tags also (had to put the big block of initial code above the initial <HTML> at the top of the page).


What needs to be figured out now, is:

(1) how to get the content of the output php page (which is accurate) to be saved into an html file.  There are a great many people on the Internet asking the same question, and I have dredged through endless "guides" without finding anything that actually works.

One that looked as if it might work, was:

<?
$file_to_parse="http://ccgi.rollobks...hvocab004.php";
$out_put_file = "http://ccgi.rollobks...alendar02.htm";
exec(" -f $file_to_parse > $out_put_file");
?>


(but it didn't)  It gave me a blank output page (so presumably there weren't any faults in the coding) but it didn't seem to actually "do" anything or rewrite the French calendar02 file I had stuck up on line to see if it would be overwritten (at http://ccgi.rollobks.../calendar02.htm ).

Well blow me down.  The Polish has overwritten the French :)))

however I haven't the vaguest idea (yet) what achieved this.  There were quite a lot of attempts at call-up pages ..... and there was also a cron script to (supposedly) get the Apache server to make the change at 4pm UK time.

Okay.  I have put a calendar03.php file up (English-Polish) at http://ccgi.rollobks.../calendar03.php , and put a calendar03.htm file (English-French) at http://ccgi.rollobks.../calendar03.htm .[Should explain at this point, incidentally, that the Polish content is only from a test database and the finished version may differ in some respects; the French-English worksheets are part of an existing on-line resource.]

Now to figure out what caused the change .....

(*pause of several hours*)

Eureka.  I have located the script ..... and, after testing, discovered that not only will it do this for more than one file at a time, but also it doesn't need an html file with the correct output url, already in place on the server, to have the content replaced.  Awesome :)

New files (for which I created only the PHP pages calendar05.php, calendar06.php and calendar07.php) have just output as http://ccgi.rollobks.../calendar05.htm , http://ccgi.rollobks.../calendar06.htm and http://ccgi.rollobks.../calendar07.htm .

And this is the script that was used to create static html pages from the php output pages:

<?php
function wwwcopy($link,$file)
{
$fp = @fopen($link,"r");
while(!feof($fp))
{
$cont.= fread($fp,1024);
}
fclose($fp);
$fp2 = @fopen($file,"w");
fwrite($fp2,$cont);
fclose($fp2);
}
//Example on using this function
wwwcopy("http://ccgi.rollobks...calendar05.php", "calendar05.htm");
//Another example
wwwcopy("http://ccgi.rollobks...calendar06.php", "calendar06.htm");
//Another example
wwwcopy("http://ccgi.rollobks...calendar07.php", "calendar07.htm");
?>


Code from a guy called almahdi on page http://www.webhostin...p/t-402191.html .  Did a look-up on Google for part of the code I'd used, in the end, for somehow it wasn't amongst the 32 references I'd bookmarked whilst looking and trying things out .....  Awesome guy.  This script is going to save me *so* much work ..... :)))   and lots of other people too, probably.

Cannot quite believe I have been able to find my way through some sort of basic knowledge of both mysql and PHP in the space of 48 hours :)  have to admit it has taken me four and a half *years* to actually embark on fathoming all this out.  You would not believe the workarounds I have done to avoid tackling learning these two :)

Not there, yet, of course!  but I've probably got enough of the basics to enable me to do what I need to, for now.  Seemingly I'm going to need to create the 471 php files separately (PHP/mysql text as per original post, with the individually-relevant line number pasted in for each) though I guess I might learn to automate that side of things eventually ;)  Long way to go but I feel like I've taken my first real steps along the road.  Starting to understand why the rest of you find php/mysql so fascinating, maybe.

And yes this is very much "another book", "part two", Steve :)  guess I don't go in for short posts much.  Though the one at http://www.phpfreaks....html#msg444978 was somewhat briefer ...

Hope the info/scripts I've put on line here will be of interest/use to others, along the way :)  I'll be back - probably!  just got rather a lot else to deal with, right now.

Best regards,

Penny.

Edited to remove one of the links [might have to remove more, later, as a number of the file refs will be used for other languages in due course].
Penny Rollo

usually at http://www.happychild.org.uk




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users