Count rows of a query sql

JcAndres · Sep 13, 2008

hi ...

i have 2 questions

1-----

i'm trying to make a validation, if a report number exist or not in database. for this i use the option for execute a script php when form its submited (("as soon as form submited (simple eval)"))

firts i check data sent by form with

echo "<pre>";print_r($aData);echo "</pre>";
exit;

Code:

Array
(
    [inc_reporte___reporteid] => 35
    [inc_reporte___reportecliente] => 1
    [inc_reporte___reportenumero] => 12345
    [inc_reporte___reportemodelo] => 2
    [inc_reporte___reporteserie] => kjh9876987
    [inc_reporte___reportefalla] => 4
    [inc_reporte___reporteciudad] => 56
    [inc_reporte___reportesla] => 4
    [inc_reporte___reportefapertura] => 13-09-2008 00:00
    [inc_reporte___reportefgendada] => 
    [inc_reporte___reportefatencionos] => 
    [inc_reporte___reportefcierrefinal] => 
    [inc_reporte___reportecomentario] => Prueba8
    [inc_reporte___reporteuser] => admin
    [tableid] => 7
    [fabrik] => 7
    [task] => processForm
    [rowid] => 35
    [Itemid] => 4
    [option] => com_fabrik
    [form_id] => 7
    [fabrik_frommodule] => 
    [fabrik__cursor] => 18
    [fabrik__total] => 19
    [returntoform] => 0
    [fabrik_referrer] => http://localhost/santander/index.php?option=com_fabrik&task=viewTable&tableid=7&Itemid=4
    [layout] => default
    [jdda468b6c86c17a56539de5c9bcf761d] => 1
    [fabrik_repeat_group_7_counter] => 1
    [Submit] => Guardar
)

next i write this code in configuration of form in the option for execute php script.

PHP:

global $database;
$reporte = mosGetParam($_POST, 'inc_reporte___reportenumero');
$sql = "select * from inc_reporte where reportenumero = $reporte";
$database->setQuery($sql);
$database->query();
if (mysql_num_rows($sql) > 0){
echo ('reporte ya ingresado');
exit;
}
else {
echo ('Reporte no ingresado');
}

my principal problem it's that i cant count rows give it for the query.

2---
when report number exist can i show a msg similar to msg of validation?

actually i use fabrik 1.0.6 svn 1028 with joomla 1.0.15.

great from the end of the earth...

cheesegrits · Sep 13, 2008

Try this:

PHP:

global $database; 
$database->setQuery("
    SELECT COUNT(*)
    FROM inc_reporte
    WHERE reportenumero = '" . intval($aData['inc_reporte___reportenumero']) ."'
");
$numrows = $database->loadResult();

In other words, use the MySQL built in COUNT() function, so it just returns a simple integer, rather than loading every row in the table into memory.

When you say "similar to validation message", it depends what you mean. If you just want to output something before your default "thankyou" message, then a simple echo should work (as long as you don't have a jump page set).

-- hugh

JcAndres · Sep 13, 2008

firt level .. ok ..

Hi cheesegrits...

ok validation its ok ... thanks for your help. - Level 1 Finished ..

Level 2 ----

the message that i tray to show its similar to message that are show when a element with a "no empty" validation its empety... but thinking this option would be complex ... or not? anyway in the image there is a example.

really that i need its a simply message that say with javascript alert "Reporte ingresado" and that allow to user go back and dont lose data inserted on form.

Any suggestion for do it?

PD: Before i dont ask you authorization for translate and resume your answer. is there some problem if i translate and resume your answers?

thanks for your help.

geat from the end of the earth ...

cheesegrits · Sep 13, 2008

No need to ask permission - I really appreciate you taking the time to summarize and translate. We have a lot of Spanish users, and it really is nice seeing someone who takes a little extra time to help others. I presume you saw my 'thank you' message on your previous post? I wrote it in English and use Ubiquity in FireFox to translate it in-situ (I love Ubqituity! just select the text, ctrl-space, "translate this to spanish"), I hope it didn't come out saying something rude in Spanish, LOL!

OK, I'm slightly confused. Is the inc_reporte___reportenumero element a database join to the inc_reporte table?

-- hugh

JcAndres · Sep 13, 2008

ok

i have a table with the estructure that u cna see in the first post, there are 14 element, the primary key its a number generated by auto increment this elements is "reporteid". also this table have other element "reportenumero" this elements have a large of chars and must be unique. in facts this elements would be the PK.

so i need:

firts check that this "reportenumero" o report number inserted by user dont exist in database, with the first code that u give me this its ok. in this moment i can know if reporte numer iserted exist or not.

now if already i know if report number exist or not, would be informate to user. in case that number report dont exist only save the data in database and show the table with the new element add.

But if report number exist i need informate to user about this and allow goback to form without delete data already inserted in form.

table inc_reporte only have database join in this elements Cliente - Falla - Ciudad.

reportenumero its a text field with max chars set to 15.

still confused ... maybe if i make a draw or a doc type excel you can undestand me. i know that i can be confuse.

thank for your help and this solution will be translated too ...

cheesegrits · Sep 13, 2008

So there is a requirement for the user to supply the report number? So we can't just use the auto-incremented primary key of this table as the report number?

-- hugh

JcAndres · Sep 14, 2008

Because the report number isn't generated for our aplication. it's generated by a system of a customer. And we dont have access to thats system.

it's for thats that report number must be inserted by user. and for this i need to make a validation.

cheesegrits · Sep 14, 2008

OK, it looks like we'll need to make a small code change to Fabrik itself to let you use a PHP form submit script to act as a validation.

I'm working on it now.

-- hugh

JcAndres · Sep 14, 2008

ok ... will wait your help...

cheesegrits · Sep 14, 2008

If I haven't gotten back to you by late on Monday, bump this thread.

-- hugh

JcAndres · Sep 15, 2008

good afternoon - Buenas Tardes ...

Bump - Subiendo!!

cheesegrits · Sep 15, 2008

OK, I've added a new little feature to the 1.0.6 SVN, which lets form submit scripts raise validation errors. All you have to do is add your error message to an array, as follows:

PHP:

global $database; 
$database->setQuery("
    SELECT COUNT(*)
    FROM inc_reporte
    WHERE reportenumero = '" . intval($aData['inc_reporte___reportenumero']) ."'
");
$numrows = $database->loadResult();
if (intval($numrows) > 0) {
    $arCustomErrors['inc_reporte___reportenumero'][] = 'This report number already exists!';
}

By adding that $arCustomErrors entry, you should trigger a validation error, and the message you set will be displayed by the element you specified, just as if it had failed a normal Fabrik validation.

-- hugh

JcAndres · Sep 16, 2008

Thanks for your help thits it's cool...

//Spanish

Como Validar si un dato ya existe en la base de datos.

Mi requerimiento consistia en obtener un valor atraves de los formularios, para este caso un numero de reporte y validar si estaba ingresado o no en la base de datos.

Para realizar la validacion hice lo siguiente:

1- Obviamente tener preparado mi formulario con sus correspodientes elementos.

2- Verificar como son entregados los datos por el formulario. esto lo hacemos insertado este codigo en la configuracion del formulario, en el area de "script a ejecutar una vez que el formulario sea enviado"

PHP:

echo "<pre>";print_r($aData);echo "</pre>";
exit;

en el menu "procesar script Curlt" seleccionanms la opcion "tan pronto como el formulario sea enviado - evaluacion simple" (as soon as form submitted - siple eval) y salvamos los cambios.

nos vamos a insertar un nuevo elemento y al momento de guardar nos deberia mostrar algo similar a esto

Code:

Array
(
    [inc_reporte___reporteid] => 35
    [inc_reporte___reportecliente] => 1
    [inc_reporte___reportenumero] => 12345
    [inc_reporte___reportemodelo] => 2
    [inc_reporte___reporteserie] => kjh9876987
    [inc_reporte___reportefalla] => 4
    [inc_reporte___reporteciudad] => 56
    [inc_reporte___reportesla] => 4
    [inc_reporte___reportefapertura] => 13-09-2008 00:00
    [inc_reporte___reportefgendada] => 
    [inc_reporte___reportefatencionos] => 
    [inc_reporte___reportefcierrefinal] => 
    [inc_reporte___reportecomentario] => Prueba8
    [inc_reporte___reporteuser] => admin
    [tableid] => 7
    [fabrik] => 7
    [task] => processForm
    [rowid] => 35
    [Itemid] => 4
    [option] => com_fabrik
    [form_id] => 7
    [fabrik_frommodule] => 
    [fabrik__cursor] => 18
    [fabrik__total] => 19
    [returntoform] => 0
    [fabrik_referrer] => http://localhost/santander/index.php?option=com_fabrik&task=viewTable&tableid=7&Itemid=4
    [layout] => default
    [jdda468b6c86c17a56539de5c9bcf761d] => 1
    [fabrik_repeat_group_7_counter] => 1
    [Submit] => Guardar
)

En donde se nos muestra el nombre de nuestro elemento que usaremos para hacer la validacion y el valor que se envia a la base de datos.

-Nombre tabla--Nombre elemento --- Valor Enviado por el formulario
[inc_reporte___reportenumero] => 12345

3- Una vez que ya tenemos identificado el elemento a evaluar procedemos a eliminar el codigo que habiamos insertado anteriormente y escribimos el codigo que queramos ejecutar para realizar la validacion. En este caso el codigo que usaremos, me entrega un valor con el numero de filas que cumplen con la condicion que definamos.

PHP:

global $database; 
//inicio de la consulta
$database->setQuery("
    SELECT COUNT(*)
    FROM inc_reporte
    WHERE reportenumero = '" . intval($aData['inc_reporte___reportenumero']) ."'
");

//asignamos el valor devuelto por la consulta a una variable
$numrows = $database->loadResult();
//definimos la condicion que se debe cumplir para que arroje un error, que en este ejemplo el numero de filas no debe ser mayor a cero
if (intval($numrows) > 0) {    
//Con esto gatillamos que al enviar el form con nuestros datos 
$arCustomErrors['inc_reporte___reportenumero'][] = 'This report number already exists!';
}

4- el codigo anterior debe ir en el area "Codigo php a ejecutar cuando el formulario sea enviado".
En el menu "procesar script Curlt" seleccionamos la opci?n "tan pronto como el formulario sea enviado - evaluaci?n simple" (as soon as form submitted - siple eval) y salvamos los cambios.

Con eso solo nos queda probar ... ingresamos datos, guardamos y volvemos a agregar datos con nuestro formulario con la particularidad que en el campo a validar pondremos la misma informacion que agregamos anteriormente. Si todo nos salio bien deber?amos ver nuestro mensaje de error sin haber perdido el resto de datos que hayamos ingresado en el formulario.

Espero no haber sido muy enredado, de todos modos cualquier correcion o aclaracion es bienvenida.

Mis agradecimientos a cheesegrits por la ayuda prestada.

Saludos Desde Chile

Count rows of a query sql

JcAndres

Member

cheesegrits

Support Gopher

JcAndres

Member

Attachments

cheesegrits

Support Gopher

JcAndres

Member

cheesegrits

Support Gopher

JcAndres

Member

cheesegrits

Support Gopher

JcAndres

Member

cheesegrits

Support Gopher

JcAndres

Member

cheesegrits

Support Gopher

JcAndres

Member

Useful links

Members online