How to Joomla-ize this MySQL/PHP code

[ddmobley]

Can someone tell me how to Joomla-ize this code:

Code: Select all

   $tablename          = "jos_content";
   $next_increment    = 0;
   $qShowStatus       = "SHOW TABLE STATUS LIKE '$tablename'";
   $qShowStatusResult    = mysql_query($qShowStatus) or die ( "Query failed: " . mysql_error() . "<br/>" . $qShowStatus );
   $row = mysql_fetch_assoc($qShowStatusResult);
   $next_increment = $row['Auto_increment'];

I tried:

Code: Select all

   $query = "SHOW TABLE STATUS LIKE '#__content'";
   $database->setQuery($query);
   $newid = $database->loadResult();
   $row = mysql_fetch_assoc($newid);
$next_increment = $row['Auto_increment'];

And:

Code: Select all

   $query = "SHOW TABLE STATUS LIKE '#__content'";
   $newid = $database->setQuery($query);
   $row = mysql_fetch_assoc($newid);
$next_increment = $row['Auto_increment'];

But that didn't work.  I kept getting:

"Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in blah.php on line blah"

Little help?

[Pentacle]

Code: Select all

$rows = $database->loadObjectList();

instead of mysql_fetch_assoc() use this above line and then do a var_dump for $rows variable to see what it has.

[CirTap]

Hi,
JDatabase' method equivalent of mysql_fetch_assoc() is called ... fetchAssoc() ;)
See more of its general interface here: http://api.joomla.org/Joomla-Framework/ ... abase.html
and the MySQL specific implementation there: http://api.joomla.org/Joomla-Framework/ ... MySQL.html

@ddmobley: once you use the $database object you SHOULD NOT (have a need to) use any of the native mysql_* functions.
The whole point of JDatabase is to wrap the different APIs of the mysql and mysqli connectors into a common interface -- or whatever other connectors will be supported in the future.
If you believe one of your sql statements don't work with any of the interface functions, you may -- at your own risk -- use the (private) $database->_resource property and pass it as the resource identifier to the "better suited" native mysql_* function in question. However, you SHOULD NOT ever have a need to do this, if you're familiar with the JDatabase interface

As for your problem: loadResult () - "This method loads the first field of the first row returned by the query."
thus is does NOT return a MySQL resource identifiere by "the result" of your query.. hence its name.

Have fun,
CirTap

[ddmobley]

Code: Select all

$rows = $database->loadObjectList();

instead of mysql_fetch_assoc() use this above line and then do a var_dump for $rows variable to see what it has.

The code now looks like this:

Code: Select all

   $query = "SHOW TABLE STATUS LIKE 'jos_content'";
   $newid = $database->setQuery($query);
   $rows = $database->loadObjectList();
   echo var_dump($rows); 

And will produce these results on the screen:

Code: Select all

array(1) {
  [0]=>
  object(stdClass)(18) {
    ["Name"]=>
    string(11) "jos_content"
    ["Engine"]=>
    string(6) "MyISAM"
    ["Version"]=>
    string(1) "9"
    ["Row_format"]=>
    string(7) "Dynamic"
    ["Rows"]=>
    string(2) "22"
    ["Avg_row_length"]=>
    string(4) "1042"
    ["Data_length"]=>
    string(5) "48532"
    ["Max_data_length"]=>
    string(10) "4294967295"
    ["Index_length"]=>
    string(4) "8192"
    ["Data_free"]=>
    string(5) "25592"
    ["Auto_increment"]=>
    string(2) "65"
    ["Create_time"]=>
    string(19) "2007-12-13 20:04:17"
    ["Update_time"]=>
    string(19) "2007-12-13 23:29:06"
    ["Check_time"]=>
    NULL
    ["Collation"]=>
    string(17) "latin1_swedish_ci"
    ["Checksum"]=>
    NULL
    ["Create_options"]=>
    string(0) ""
    ["Comment"]=>
    string(0) ""
  }
}

How do I reference the Auto_increment value codewise?  "echo $rows['Auto_increment'];" wouldn't display anything.  And also, in my code above, I have to reference "jos_content" rather than "#__content".  Why is that?

I should point out to CirTap that I am working in Joomla v1.0.13, not v1.5.  The API references he provided are for v1.5 and can't help me.  And I didn't see a fetchAssoc() function at all.

[CirTap]

Hi,
in 1.0.x it's called loadAssocList()

As the name loadObjectList() may imply, it returns a list of objects, however in this case the list ( = array) contains a single entry (object(stdClass)) only:
  $rows[0]->Auto_increment
You should be able to use "#__content" with setQuery(). No idea why it does not work in your case.

CirTap

[CirTap]

here's the 1.0 database stuff: http://help.joomla.org/content/category/12/108/125/
just saw loadRow(), loadResultArray() and many others. Use the one that suits your need -- I suppose loadRow() is more appropriate for this particular statement.

CirTap

[ddmobley]

Hi,
in 1.0.x it's called loadAssocList()

As the name loadObjectList() may imply, it returns a list of objects, however in this case the list ( = array) contains a single entry (object(stdClass)) only:
  $rows[0]->Auto_increment
You should be able to use "#__content" with setQuery(). No idea why it does not work in your case.

I figured out about loadAssocList() from scanning through database.php.  Thanks for the reference to the 1.0 API docs.

I tried $rows[0]->Auto_increment but it wouldn't display anything.  Here's what I did to make it work:

Code: Select all

   $query = "SHOW TABLE STATUS LIKE 'jos_content'";
   $newid = $database->setQuery($query);
   $rows = $database->loadAssocList($newid);
   foreach($rows as $row) {         
      $newid = $row['Auto_increment'];
   }

Why it won't work with #__content is beyond me, but it won't.  Some weird stuff here.

[ddmobley]

just saw loadRow(), loadResultArray() and many others. Use the one that suits your need -- I suppose loadRow() is more appropriate for this particular statement.

loadRow wouldn't work.  Neither would loadResultArray. Only loadAssocList would work.  Weird.

[ddmobley]

Why it won't work with #__content is beyond me, but it won't.  Some weird stuff here.

It will however work with:

Code: Select all

   $query = "SHOW TABLE STATUS LIKE '%_content'";

I just brought in the database prefix variable as a global and ran with:

Code: Select all

   $query = "SHOW TABLE STATUS LIKE '" . $mosConfig_dbprefix . "content'";

Final version:

Code: Select all

   $query = "SHOW TABLE STATUS LIKE '" . $mosConfig_dbprefix . "content'";
   $database->setQuery($query);
   $rows = $database->loadAssocList();
   foreach($rows as $row) {         
      $newid = $row['Auto_increment'];
   }

One might wonder what I am doing.  I am working on an article submission component that uploads a number of images, and the image upload process creates directories that are named the same as the articles ID number under the images directory.  So I need the next ID number that is getting ready to be used for the article being submitted to assign to the image directories.

[CirTap]

Hi,

so all you need is the incremented record id?
Try $database->insertid() right after you run the INSERT query, and you should receive the last auto_increment of that table.
You also get the last insert id using the 3rd argument of:
  $database->insertObject( $table, &$object, $keyName)
i.e.

$object = new stdClass; // simple value object
$object->field1 = "foo"; // each property represents a table column
$object->field2 = "bar";
// creates INSERT INTO jos_mytable (field1,field2) VALUES ("foo","bar")
$database->insertObject("#__mytable", $object, "id");
echo $object->id;

The reason why %_content works is that "%" is a wildcard character for LIKE, the equivalent of "*"

CirTap

[ddmobley]

so all you need is the incremented record id?
Try $database->insertid() right after you run the INSERT query, and you should receive the last auto_increment of that table.
You also get the last insert id using the 3rd argument of:
  $database->insertObject( $table, &$object, $keyName)

I just need the next id number before I upload my images, so I can create the appropriate directory for the images. I need to do this before the content is saved so I can submit the image paths to $row->urls, to be saved with the content.  If I wait until after the content is added to request the record id, the record is already saved and I would need to resave it with the $row-urls data updated.  It works though, so I'm happy.

The reason why %_content works is that "%" is a wildcard character for LIKE, the equivalent of "*"

That's why I used it!    But I went with the latter, the use of the configuration prefix variable.

[bao_jiffy]

Not sure that I followed all of the steps in this thread but I have to ask 3 questions.  One for my benefit and another 2 hopefully for your benefit.

Would a query of select max(id) from `#__my_table` not get you the last inserted auto_increment value prior to your insert?  You may have this all ready covered but I thought that I would ask any how.

Also, I found that I had to through the ` quote marks around my table names to make them inherit the database prefix using the #_.  Not sure if that helps your cause any but thought that I would put in my 2 cents there.

Now to MY question.  Is there a simple example somewhere of reading a list of data into a simple form, edit a record or multiple records, then update the data without having to go through several php files?  I am guessing that I just need to recall the same php when I complete my edits but I just can't seem to figure it out.  I can create a basic form but if multiple records are returning, what is the proper way of editing a record and storing the results?

Thanks for any pointers, even if it is to a manual or sample component.

[ddmobley]

Not sure that I followed all of the steps in this thread but I have to ask 3 questions.  One for my benefit and another 2 hopefully for your benefit.

Would a query of select max(id) from `#__my_table` not get you the last inserted auto_increment value prior to your insert?  You may have this all ready covered but I thought that I would ask any how.

No, that was the first iteration I tried.  Here's why it won't work accurately: What if the most recent record was deleted, but the max pointer was still somewhere else.  Let's say you had just added record #58.  The next record obviously is going to be record #59, but if you deleted #58 and did as you say, check the highest record id for that table, it would return #57.  You would think the next record was #58 but it would actually be #59 in the auto_increment value.  I did this, and after deleting the most recent four records, my incrementing structure was four numbers off, which is why I had to figure out a way to query the table status for the auto_increment value.

Also, I found that I had to through the ` quote marks around my table names to make them inherit the database prefix using the #_.  Not sure if that helps your cause any but thought that I would put in my 2 cents there.

That seems to work with most queries, but would not work for the SHOW TABLE STATUS query.

Now to MY question.  Is there a simple example somewhere of reading a list of data into a simple form, edit a record or multiple records, then update the data without having to go through several php files?  I am guessing that I just need to recall the same php when I complete my edits but I just can't seem to figure it out.  I can create a basic form but if multiple records are returning, what is the proper way of editing a record and storing the results?

Thanks for any pointers, even if it is to a manual or sample component.

It's very easy to use the same PHP page to both display an empty form and then submit and save the data.  Consider how Joomla does it all with one index.php!  Your PHP page will have two (or more) "sections" what are controlled by access flags set in the form variables and accessed by IF-THEN statements or CASE statements.  Consider the logic of:

Code: Select all

If (the page has no form values being submitted) {
   Display the form code;
} else {
   Save the form values being submitted;
}

Open up com_content.php and look at how it's done.  The page's functions are accessed based on the value of $task.  If the task is edit, do this function. If the task is copy, do this function.  If the task is save, do this function. 

Hopefully you will see the structure of how to use one PHP file to accomplish multiple tasks without getting confused by everything else around you.