Grabbing data and sending to DB

[wookie]

On my coppermine gallery I have a modification running that outputs data to a page like below, it prints around 10-20 of these daily, what I want to be able to do is grab this info and put it into a new database table with each line below in its own field, how woould I go about that, where do I start?

 

 

Photo Title!

by Wookie

20-Jan-2008

Pic Rating : 19000

Votes : 4

Pid Number : 36126

 

Photo Title!

by Anon

19-Jan-2008

Pic Rating : 16000

Votes : 2

Pid Number : 36117

 

Photo Title!

by Admin

17-Jan-2008

Pic Rating : 12666

Votes : 1

Pid Number : 35881

 

Many thanks in advance!

 

Wookie

[revraz]

Add code so when it outputs the data it also inserts to a DB.

[wookie]

Yes, thats what I was asking, how do I go about that?

 

Many thanks

 

Wookie

[Nhoj]

Create a new table with the setup you'd like, locate the file that outputs the lines you described and add a query to insert them into the newly created table when they are outputting them.

[revraz]

If you don't know PHP, you'll have to learn some in order to do it.

[wookie]

If you don't know PHP, you'll have to learn some in order to do it.

 

And I did post this in PHP help?

 

Mainly because I know a little php but dont know enough to complete the task, and basically you've told me to go and do what I've already written that I need help with.

 

Many thanks

 

Wookie

[revraz]

Just wanted to make sure you understood the purpose of the forum is all.

[wookie]

Thanks for the clarification revraz, however, my question remains and I reiterate from my first post and ask once again for help. Can anyone help me at all? Its the grabbing of the info that I do not know how to do.

 

Many thanks

 

Wookie

[adam291086]

How are you outputting that data? Do you have a database set up?

[wookie]

Yes database is set up. (Its a coppermine gallery)..I have not as yet set up the new table for the info but that can be easily done.

 

I just dont know how to grab this info before its printed to the page, currently reading tuts to find a solution but none appear to be help as I'm not even sure what to search for.

 

Wookie

[adam291086]

Have you tried posting in the coppermine support forum. I have never used it and therefore don't know which script outputs that little report. If you can find that script it will be easy to input it into a database

[wookie]

Yes I have tried posting there in the past....once bitten twice shy!

 

Running an insert query I have no problem with, its the code I need to grab it that I dont know.

 

Wookie

[adam291086]

You need to locate the script that generate the output. Once you have found that you can then manipulate it a little to input into your database.

[wookie]

Yes I have that script, so I know what outputs the info.

 

Wookie

[wookie]

This is the function

 

function getTopRatedMediaFrom ($source, $count = 1) {
$resultset = array();
if (is_numeric($count)) {

	$sourceSql = $this->makeSourceSql($source);

	if ($sourceSql != "") $sourceSql = " AND " . $sourceSql;

	$sqlcode = "SELECT {$this->sqlPostSelect} " . $this->sqlSelect . " FROM "
		. $this->sqlTableSelect
		. " WHERE 1 "
		. $this->sqlUserDataLink 
		. $this->filetypefilter ." AND p.approved='YES' AND p.pic_rating > 0 "
		. $sourceSql . " {$this->privacyfilter} "
		. " ORDER BY p.votes DESC LIMIT 0,$count";

		$resultset = $this->dbExecuteSql($sqlcode);
		$this->addPathInfo($resultset);
}
elseif ($this->cfg['cfDebugMode'] == 'true'){
	debugPrint("Non numeric count submitted");
}

return ($resultset);
}

And here is the code that uses it and uotputs what I posted top of page.

 

<?php    

  include_once "./cpmfetch/cpmfetch.php";
  $objCpm = new cpm("./cpmfetch/cpmfetch_config.php");
  $options = array("subtitle" => "{{pTitle}} <br />by {{pOwner_name}}<br /><br />{{pCtimeFormatted}}<br />  Pic Rating : {{pPic_Rating}} <br /> Votes : {{pVotes}} <br /> Photo Number : {{pPid}} <hr />",

                "imagesize" => "thumb");	
  $objCpm->cpm_setReturnType("resultset");

  $objCpm->cpm_viewTopRatedMediaFrom ("cat=1",100,1,$options);

  $objCpm->cpm_close();
   
?>

 

Hopefully this will help someone, help me to get started.

 

Many thanks in advance!

 

 

 

Wookie

[adam291086]

I am not sure here, but i can tell that $options holds an array with the informaiton in. You need to get that inforamtion out of the array and then input it into the database.

 

 

 

[wookie]

Yup, this is the bit that I dont know how to do I guess...am looking over "arrays" at php.net but so far have drawn a blank with what I need to do.

 

Wookie

[adam291086]

I am not 100% sure on this either but you could try

 

<?php    

  include_once "./cpmfetch/cpmfetch.php";
  $objCpm = new cpm("./cpmfetch/cpmfetch_config.php");
  $options = array("subtitle" => "{{pTitle}} <br />by {{pOwner_name}}<br /><br />{{pCtimeFormatted}}<br />  Pic Rating : {{pPic_Rating}} <br /> Votes : {{pVotes}} <br /> Photo Number : {{pPid}} <hr />",

                "imagesize" => "thumb");	
  $objCpm->cpm_setReturnType("resultset");

  $objCpm->cpm_viewTopRatedMediaFrom ("cat=1",100,1,$options);

  $objCpm->cpm_close();

//test 

echo "this is a test to see what is echoed". {{pTitle}};
   
?>

[wookie]

 

 

 

I've modified things slightly to return the array. Here is the file that now calls it

 

 

<?php    

  include_once "./cpmfetch/cpmfetch.php";
  $objCpm = new cpm("./cpmfetch/cpmfetch_config.php");
  $options = array("subtitle" => "{{pTitle}} <br />by {{pOwner_name}}<br /><br />{{pCtimeFormatted}}<br />  Pic Rating : 

{{pPic_Rating}} <br /> Votes : {{pVotes}} <br /> Photo Number : {{pPid}} <hr />",
                "imagesize" => "thumb", "noimage" => "");
$objCpm->cpm_setReturnType("resultset");
$table = $objCpm->cpm_viewTopRatedMediaFrom ("cat=1",100,1,$options);
print_r($table) ;
$objCpm->cpm_close();

?>

 

 

Using print_r, I now get this below. I'm still very much out of my depth and need help with writing (or pointing in the right direction) the php that will grab the info below and insert it into a new database table, here are just two of the sets numbered 0 and 1, next would be 2, 3 etc. Looking at the sets below would I need to call the fields in my database table: "pFilepath" "pFilename" and then in the php file above write an INSERT db query, this is what is really confusing me so any help is very much appreciated.

 

 

Array ( [0] => Array ( [pFilepath] => userpics/10004/ [pFilename] => Autumn_morning.jpg [pAid] => 3 [pFilesize] => 204624 [pTitle] => Autumn morning [pCaption] => The field opposite my home [pOwner_name] => Mike Young [pOwnerId] => 4 [pCtime] => 1193671357 [pHits] => 2 [pPid] => 7 [pPic_Rating] => 100000 [pVotes] => 100 [pWidth] => 1000 [pHeight] => 731 [pUser1] => [pUser2] => [pUser3] => [pUser4] => [cCid] => [cName] => [cDescription] => [cPos] => [cParent] => [cThumb] => [aAid] => 3 [aTitle] => Mikes bits and bobs [aDescription] => [aVisibility] => 0 [aPos] => 1 [aCategory] => 10004 [aThumb] => 0 [aKeyword] => [fullPathToThumb] => albums/userpics/10004/thumb_Autumn_morning.jpg [fullPathToNormal] => albums/userpics/10004/normal_Autumn_morning.jpg [fullPathToFull] => albums/userpics/10004/Autumn_morning.jpg [cpmSubtitle] => Autumn morning 
by Mike Young

29-Oct-2007
Pic Rating : 100000 
Votes : 100 
Photo Number : 7 
--------------------------------------------------------------------------------
) [1] => Array ( [pFilepath] => userpics/10002/ [pFilename] => Conneaut-lighthouse.jpg [pAid] => 1 [pFilesize] => 85197 [pTitle] => Lighthouse [pCaption] => this is a test [pOwner_name] => windsurf [pOwnerId] => 2 [pCtime] => 1193669144 [pHits] => 3 [pPid] => 1 [pPic_Rating] => 100000 [pVotes] => 10 [pWidth] => 828 [pHeight] => 980 [pUser1] => [pUser2] => [pUser3] => [pUser4] => [cCid] => [cName] => [cDescription] => [cPos] => [cParent] => [cThumb] => [aAid] => 1 [aTitle] => Windsurf [aDescription] => [aVisibility] => 0 [aPos] => 1 [aCategory] => 10002 [aThumb] => 0 [aKeyword] => [fullPathToThumb] => albums/userpics/10002/thumb_Conneaut-lighthouse.jpg [fullPathToNormal] => albums/userpics/10002/normal_Conneaut-lighthouse.jpg [fullPathToFull] => albums/userpics/10002/Conneaut-lighthouse.jpg [cpmSubtitle] => Lighthouse 
by windsurf

29-Oct-2007
Pic Rating : 100000 
Votes : 10 
Photo Number : 1

 

Many thanks for any help and advice.

 

Wookie

[wookie]

sql = "INSERT INTO table_name (a1, a2)

VALUES ('" & pFilepath & "','" & pFilename & "')"

 

Would this be the way to go?

 

Wookie

[wookie]

I'm using this insert query now and although I am getting no errors, nothing is being put into the table, can anyone spot if there is a mistake, something I missed or dont know about? The array I'm attempting to get the values from is a couple of posts above this one.

 

 

    $sql = "INSERT INTO {$CONFIG['TABLE_POTD']} ".

          "(filepath, filename) ".

          "VALUES ('$pfilepath', '$pfilename')";

 

    $result = cpg_db_query($sql)

 

 

Many thanks in advance!

 

Wookie

[wookie]

All array info or example I have seen use ` or "  whereas my array is using [] is this likely to be causing the problem. I'm really quite lost now and struggling. I'm quite prepared to read up on something and learn it if I can get the end result, I just dont know what I should be looking for or reading.

 

Wookie

[wookie]

Bump

[wookie]

Please can someone help...4 days now and less hair than Homer remains.

 

Many thanks.

 

Wookie

[wookie]

I've looked over using arrays at many sources, but most appear to be specifying an array within that php file, not one that is being output by a file. In the following array pFilepath has the value "userpics/10004/" I want to grab this value and insert it into a new databse table called "test" in the field name "filepath"

 

Array ( [0] => Array ( [pFilepath] => userpics/10004/ [pFilename] => Autumn_morning.jpg [pAid] => 3 [pFilesize] => 204624 [pTitle] => Autumn morning [pCaption] => The field opposite my home [pOwner_name] => Mike Young [pOwnerId] => 4 [pCtime] => 1193671357 [pHits] => 2 [pPid] => 7 [pPic_Rating] => 100000 [pVotes] => 100 [pWidth] => 1000 [pHeight] => 731 [pUser1] => [pUser2] => [pUser3] => [pUser4] => [cCid] => [cName] => [cDescription] => [cPos] => [cParent] => [cThumb] => [aAid] => 3 [aTitle] => Mikes bits and bobs [aDescription] => [aVisibility] => 0 [aPos] => 1 [aCategory] => 10004 [aThumb] => 0 [aKeyword] => [fullPathToThumb] => albums/userpics/10004/thumb_Autumn_morning.jpg [fullPathToNormal] => albums/userpics/10004/normal_Autumn_morning.jpg [fullPathToFull] => albums/userpics/10004/Autumn_morning.jpg [cpmSubtitle] => Autumn morning 
by Mike Young

 

Please could someone help me to do this, or point me in the right direction or advise me what I need learn about to continue this.

 

Many many thanks

 

Wookie