CMS Errors + Query Failed

[sydd]

Hi,

first of all, ******, I just wrote this text, got redirected in order to log in (I was already logged in, and all the text was gone. |: bah.)

Anway, I digress...

First problem: When I try editing our CMS (content management system I think), basically adding a new column at one of our projects, after I rewrote the php file in order to actually give me a new column, it doesn't "stick". I can write in the newly created column, but when I click on Update it doesn't work. The page just refreshes and the information I provided the column just disappears.

I've done a little sketch/pic to show you what I mean, as I don't think I'm being exceptionally clear. :p

Here's the code that I added/edited in order to get the DOWNLOAD section to show up:

Main.php of the admin section (Only added code where I edited/added something):

class main{
    function showList(){
        global $module,$panel,$modul_main;
        $f             = new AGFunctionsLib();
 
        $strDefLangCode = $f->getDefaultLanguage();
        $currentLang = $_GET['clang'];
        if ($currentLang == '')
        {
        $currentLang    = $strDefLangCode;
        }
 
        switch($_GET['f']){
            case '1' : $foldername = "Active";break;
            case '2' : $foldername = "New";break;
            case '3' : $foldername = "Trashed";break;
        }
        $fields_arr = array("pr_date|Date|1","pr_name|Project name|1","pr_location|Location|1","pr_team|Team|1","pr_download|Download|1");

formstruct.php (Once again I only copied out the bits where I added something):

$formstruct = array(
 
    array(
        "name" =>    "pr_download",
        "label" => "Download",
        "objtype" => "HTMLEDITOR",
        "datatype" => "STRING",
        "dim" => "90,30",
        "showImagePallete"=>1,
        "dbfield" => "pr_download"
    ),
 
$formstructAdd = array(
 
    array(
        "name" =>    "pr_download",
        "label" => "Download",
        "objtype" => "HTMLEDITOR",
        "datatype" => "STRING",
        "dim" => "90,30",
        "showImagePallete"=>1,
        "dbfield" => "pr_download"
    ),

If you guys need the full php file in order to help me, do tell and I will provide you with it.

Next, in the AGFunctionsLib.class (I'm guessing it's the main functions library ;p) I rewrote/added stuff in order to make it show up on the website:

function prepareProjectPage($id='',$sub='',$loc=''){
    global $conf,$layout,$lang;
        $f = new AGFunctionsLib;
        $project = new AGProject();
 
        if($_GET['displaymode']=='print'){
            $strDisp = 'print';
        }else{
            $strDisp = 'full';
        }
 
---- Lines and lines of code -----
 
        /*--content banner--*/
        $strBannerFile = $f->getGeneralContent('c_pic','PROJECTS',197);
        $strBanner = "<img src='/gallery".$strBannerFile."'>";
        $layout->AddComponent('contentBanner',$strBanner);
 
        //$strContent = $project->getProjectList($id);
        if ($loc=='')
        {
        if ($sub=='')
        {
        $strContent = $f->getProjectContent('pr_desc',$id);
        }
        elseif ($sub=='1')
        {
        $strContent = $f->getProjectContent('pr_desc',$id);
        }
        elseif ($sub=='2')
        {
        $strContent = $f->getProjectContent('pr_team',$id);
        }
        elseif ($sub=='4')
        {
        $strContent = $f->getProjectContent('pr_download',$id);
        }
        elseif ($sub=='3')
        {
        $strContent = $f->getProjectContent('pr_contact',$id);
        }
        else
        {
        $strContent = $f->getProjectContent('pr_desc',$id);
        }
 
------ lines and lines of code ------
 
function getProjectSearchResult($strFieldName){
            global $conf;
            $db = new AGDBConnection();
            $rs = new AGDBRecordset();
            $f = new AGFunctionsLib();
            $dbo = $db->init($conf['dsn']);
 
 
                $sql = "select * from projects where pr_status=1 AND (pr_name like '%$strFieldName%' OR pr_location like '%$strFieldName%' OR pr_desc like '%$strFieldName%' OR pr_team like '%$strFieldName%' OR pr_contact like '%$strFieldName%' OR pr_download like '%$strFieldName%') order by pr_ID DESC";

Now here's the rub, whenever I try to use the search function, I get this:

Query Failed: Unknown column 'pr_download' in 'where clause'
select * from projects where pr_status=1 AND (pr_name like '%decentralisation%' OR pr_location like '%decentralisation%' OR pr_desc like '%decentralisation%' OR pr_team like '%decentralisation%' OR pr_contact like '%decentralisation%' OR pr_download like '%decentralisation%') order by pr_ID DESC

That error also appears whenever I try to click on the Download link (the one I added) on the project pages.

So, yeah, help me please. :p I'll happily provide you with any kind of code/file that you need, just ask. If I haven't been clear enough on something, do tell.

Thank you very much in advance,

Lorien

[Atli]

Hi.

The error you posted at the bottom tell us that you are trying to use a column that doesn't exist. A pr_download column in the projects table.

I would start by fixing that error. Make sure the column exists, and that it is spelled correctly in the query. (Assume it is case sensitive.)

If the problem persists after that, please show us the exact structure of the table.
(The result of SHOW CREATE TABLE is best)

The code where the INSERT or UPDATE queries for you update functionality are executed would be helpful to.

[sydd]

@Atli
Sorry for the late reply.

I'm guessing you mean this?

    function processUpdate($postData){
            global $dbo,$module;
            $cm = new AGDBCommand();
            //updating services
            $memberID = $_POST['pr_ID'];
            $selectedServiceArr = preg_split("/,/",$_POST['serviceSelected']);
            $provarr = preg_split('//', $_POST['serviceSelected'], -1, PREG_SPLIT_NO_EMPTY);
            $hurufpertama=$provarr[0];
            if ($hurufpertama <> ',')
            {
            $location=",".$_POST['serviceSelected'];
            }
            else
            {
            $location=$_POST['serviceSelected'];
            }
            //$location=$_POST['serviceSelected'];
                    $insSql = "UPDATE projects SET pr_location = '$location' WHERE pr_ID = $memberID";
                    $cm->exec($insSql,$dbo);
                $f = new AGFunctionsLib();
 
                $dataEdit = new AGDBForm2DB;
                $dataEdit->table = $module->table;
                $dataEdit->condition = $module->pkey." = " . $_GET['id'];
                $strSerial = date("Ymdhis");
                $dataEdit->dataUpdate();
    }
 
    function processInsert(){
            global $dbo,$module;
            $f = new AGFunctionsLib();
            $curr_date = date("Y-m-d");
            $curr_time = date("h:i:s");
            $dataAdd = new AGDBForm2DB;
            $strSerial = date("Ymdhis");
            $dataAdd->table = $module->table;
            $dataAdd->dataSave();
 
            $lastRecord = $f->getLastRecord("projects");
            $prID = $lastRecord[0]['pr_ID'];
 
            $cm = new AGDBCommand();
            $selectedService = $_POST['serviceSelected'];
                    $insSql = "UPDATE projects SET pr_location='$selectedService' WHERE pr_ID = $prID";
                    $cm->exec($insSql,$dbo);
 
            //add empty data for other languages
 
        $langQ = "select * from lang where lang_status=1 and lang_default <> 1";
        $langRS = new AGDBRecordset();
        $langRS->querynfetch($langQ,$dbo);
        foreach($langRS->arrRowResult as $rows){
            $pr_lang = $rows['lang_code'];
            $pr_code = $_POST['pr_code'];
            $pr_name = $_POST['pr_name'];
            $pr_download = $_POST['pr_download'];
            //$pr_description = $_POST['pr_description'];
            $langIns = "insert into projects(pr_lang,pr_code,pr_name,pr_status) values('$pr_lang','$pr_code','$pr_name',1)";
            $q = new AGDBCommand();
            $q->exec($langIns,$dbo);
        }
        $langRS->freeResult();
 
    }

I know that the error means that there's a missing column, but the problem is that the column IS in the CMS and on the website. I just can't edit it on the CMS or enter any data into that specific column. The problem is that the website and the CMS was designed by a company (which doesn't exist anymore) via an outside contractor, who so far hasn't replied to any of our E-Mails. So we have nobody who's familiar with the CMS. My only options are to either rewrite the CMS or completely start from scratch and make one myself. As I don't have the time for the latter, I think I'll stick to rewriting it. So far there haven't been any problems, but now... well, yeah.

Furthermore, the SHOW CREATE TABLE - to be honest I have no idea what you want me to do. I figure it's a MySQL command? If so, how do I enter it and where? Download mysql (client) and fiddle around? I have close to nil experience with MySQL programming. Sorry if I'm not being of much help.

[Atli]

Ok.

The fact that this is in the CMS doesn't really tell us much.
The error, however, does tell us the field doesn't exist in the table.

Perhaps this is something whoever originally designed this was intending to implement but left it out for some reason.

In any case, we need to see the table structure to be sure what's going on.

You need to find the MySQL username, password and database name (probably in some configuration file in your CMS), put it into the following code, put the code into a new PHP file, and execute it.

<?php
// We don't need HTML for this
header("Content-Type: text/plain");
ini_set("html_errors", false);
 
// Connect to MySQL
$dbLink = mysql_connect("localhost", "mysqlUser", "mysqlPassword")
    or die("Failed to connect to MySQL! \n". mysql_error());
mysql_select_db('mysqlDatabase', $dbLink)
    or die("Failed to select a table!\n". mysql_error());
 
// Execute the query
$sql = "SHOW CREATE TABLE `User`";
$result = mysql_query($sql, $dbLink)
    or die("Query failed!\n{$sql}\n". mysql_error($dbLink));
 
// Show the output
$row = mysql_fetch_row($result);
echo $row[1];
 
// Free resources
mysql_free_result($result);
mysql_close($dbLink);
?>

That should print the exact structure of your table.

[sydd]

Okay I think I fixed it.

Edited the config.php (had the wrong username/password/database for phpMyAdmin) and now I'm able to log into the MySQL databases. You were right, pr_download wasn't in the table where it was supposed to be, now that I've edited it works fine.

Thanks for the help anyway!

[sydd]

GNAH!

I wish for once these things do what I want them to do.

Okay I've fixed what I wanted to fix, but some old problem I never thought of in my previous posts suddenly reappeared:


When - in the CMS - I click on New (Add New Data), basically creating a new project (not the columns, they're part of the project) everything is fine until I try click on "Edit Data". Then the _content_ of one of the trashed projects suddenly appears in the columns. I don't think I'm being very clear again so here's a picture:

Step by step picture of what's wrong.

My Paint skills are amazing. :p

So here you have it, I hope I explained it properly. On a sidenote, whenever I try to DELETE the trashed items, they disappear completely (all of them) and reappear whenever I trash something else. Going crazy here.

Sorry for the spam.